Totals and Cumulative Queries in Graf-FX

Graf-FX will allow you to analyse data using any of the standard SQL aggregate function listed below the example of the different functions in the Launchpad form.

Sum Function … Returns the sum of a set of values contained in a specified field on a query.

Avg Function … Calculates the arithmetic mean of a set of values contained in a specified field

Count Function … Calculates the number of records returned by a query

Min, Max Functions … Return the minimum or maximum of a set of values contained in a specified field on a query.

StDev Function … Return estimates of the standard deviation for a set of values contained in a specified field on a query.

Var Functions … Return estimates of the variance for a population represented as a set of values contained in a specified field on a query

First, Last Functions … Return a field value from the first or last record in the result set returned by a query..

Weighted Average Function … Graf-FX also supports a non standard sql aggregate function that returns the weighted average of a set of values in a field based on the results of data in another field for like rows of data.

Setting Up An Aggregate function

In the top section of the launchpad form is a combo box that looks as follows

If you want to change the standard aggregate function from Sum to another choice, select from the list by clicking on the down arrow button Change your choice to Avg so that averages are now show as the default.

You can now click in the aggregate function choice box (currently showing Sum or Show As?) next to any of the fields that can be used in aggregate functions. This will change the selection to the current default of Avg. The same technique applies to changing the Aggregate function for any of the fields.

But it is easier to right click in a aggregate function field to retrieve a list of available functions as follows

Decimal Places

When show calculated aggregates such as Average and Standard Deviation, Access has an annoying habit of showing anything upto 16 figures of accuracy in the queries. An example is shown below where we are showing Average Sales By Region (Use Yellow ? button)

Click in the choice box as follows to show all results with 2 decimal places of accuracy.

Weighted Averages

To weight one field against another, first choose the Wavg function in the Show As combo box.

This will bring up list of numerical fields that you can weight a field by as follows

In this case we will choose to weight by Budget results so we select Wavg_Budgets. By then selecting aggregate box next to Sales, the show as option now displays Wavg_Budgets. When the actual query is developed in the data mining, a correct aggregate function will be allocated to the drilldown query

The SQL query that would be generated in this case would be

Select ProductName , sum(Sales*Budgets)/sum(Budgets) from zSales_Demo group By ProductName;

Notes On Aggregates