When Totals Don’t Work

Qlikview (QV) provides for three types of totals in straight tables and pivot tables:

• None
• The expression calculated at the total level
• Aggregate function (sum, average, minimum etc) of the table rows

These options cover most cases, but sometimes you have a table where none of these options is appropriate, or provides the correct result. As an example, consider a table of absolute variances, such as budget amount vs selling amount, on a table dimensioned by product class. The expression for the variance and % variance would be:

[AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount))

[%AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount)


These expressions yield different results for a different degree of dimensioning, so the sum of the absolute variances per ProductClass is not the same as the absolute variance at the total level. Therefore, the AbsoluteVariance column needs to be totalled using the Sum of rows option. For the %AbsoluteVariance, however, the expression at the total level does not work for the same reason, and the summing of the rows is arithmetically meaningless. In this example, a total is required for both of these columns.

So, how do we construct an expression that will produce the correct %AbsoluteVariance for each row and for the total?

We have the expression for the rows:

[%AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount)

For the total, we need an expression like:

[%AbsoluteVariance] = Sum(Rows of AbsoluteVariance) / Sum(BudgetAmount)

Assuming that ProductClass was the only dimension on the table, the Sum(Rows of AbsoluteVariance) can be calculated as follows:

Sum(Aggr(Fabs(Sum(BudgetAmount) – Sum(SellingAmount)), ProductClass))

So, how do we differentiate between the two expressions?

This is where the QV Dimensionality() function comes in. In our case, Dimensionality() will return zero when calculated on the total row, and greater than zero elsewhere. Check the Qlikview Reference Manual for more information on the function.

Our final expression becomes:

If(Dimensionality() = 0,
// total row expression
Sum(Aggr(Fabs(Sum(BudgetAmount) – Sum(SellingAmount)), ProductClass)) / Sum(BudgetAmount),
// other row expression
Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount))

QED

Unzip the download here and play around with a sample QVW model and the Excel source file.

Selection and month views

If you set up a simple month view chart such as a chart of the sum of transaction amounts by day, for example:

• using a dimension such as Day(TransDate)
• using an expression like Sum(TransAmount)

Now, if the user views this chart with no year and month selected, they will get the sum of amounts for that day of the month for all months loaded into the model. Not very useful.

Another approach is to make the chart display summed amounts for the current month. If a selection is made, then display summed amounts for the selected month/year.

This is how I do this:

1. Create TransYear and TransMonth fields on loading
2. Put listboxes with these two fields on the sheet
3. Build the chart
4. Filter the chart expression(s) with the following date filter (added filter highlighted):

Sum(If(TransDate >= MonthStart(Max(Total TransDate)) AND TransDate <= MonthEnd(Max(Total TransDate)), TransAmount)


5. Now, if nothing is selected, the TransDate range will be in the current month (or at least the last month in which a transaction was recorded - in most cases this will be the same date).

If you select a month only, then Max(TransDate) will be the month in the most recent year that contains data for that month. For example, in April: selecting Jan, Feb, Mar or Apr will display data for that month in the current year. Selecting any other month would display data for that month in the previous year.

If you select a year only, then Max(TransDate) will be the last day of that year with transactions. If that is the current year, then that will be today (or yesterday).

Any other selection (of TransDate, TransMonth, TransYear) will cause the month with of the last possible TransDate to be displayed.

You can, of course, do the filtering with a set expression if you prefer. I find this syntax more intuitive, but the final choice should depend on performance. Maybe you would like to convert this to a set expression in a comment below?