Crosstab/Pivot Queries and 3D Data Graphs

Graf-FX will allow you to analyse data using a crosstab query in conjunction with your data mining. A crosstab query displays summarized values (sums, counts, and averages) from one field in a table and groups them by one set of facts listed down the left side of the datasheet and another set of facts listed across the top of the datasheet. An example query showing Sum of Sales by Product and State has been generated below.

To generate the same query and then develop a crosstab graf, follow these steps

Select the "zSales_Demo" table

Select the field that will produce the row headings which in this example is "ProductName"

Now select the field that will produce the column Headers which in this example is "State"

Now select the field that you want to show results for which in this example is Sales

Note that we will show Sum of Sales and if you want to change the aggregate function used to display the data, right click in the field showing "Sum" as follows

Now you can show the crosstab query for level 1 using the following button

To show the crosstab as a 3D histogram graf, select the "z_grafHisto3d" graf as follows

Now you can visualise the crosstab data for the first level in 3D using the following button

This produces a graf that looks as follows.

Now we can utilise crosstab in a powerful way when data mining. In the following example we wish to show only "Cola" sales and analyse these results by Calendar quarter

Now when we select the Crosstab button on level 2 as follows

We will end up with a crosstab SQL and query results as follows

TRANSFORM sum(Sales) as sumSales Select CalYYQ_FX(SalesDate) as [CalYYQ_FX(SalesDate)] from zSales_Demo where ProductName = 'Cola' group By CalYYQ_FX(SalesDate) PIVOT State;