Expression total vs sum of rows

[Sonja_Bauernfeind]

Let's say that we have a school contest where three person teams get points by their grades in three different classes. The team may select the highest score within the group for each individual class and then add the three top scores together for a total. The following data has been read from the script:

We must now make a chart with Class as dimension and max(Score) as expression. A straight table with sum of rows will look like follows:

If we for some reason want to display this in a pivot table we run into problems. The straight table above converted into a pivot table would look as follows:

In this specific case the total of 12 is clearly what we want and 5 is equally wrong for our purposes.

Environment:

QlikView 

The QlikView straight table has a choice for its totals between a simple sum of rows and a calculated expression total. The QlikView pivot table lacks this choice. Pivot table totals are always calculated as expression total.

This is normally a good thing, since it is a rather rare occasion that a sum of rows total is relevant when the two differ (one exception is the example in the article). You should exercise extreme care when using sum of rows on any type of aggregation other than pure sums.

Resolution:

To get the same result as in the Straigh Table we enclose the original expression  in an aggr function, using the surrounding chart's dimension also as dimension in the aggr function. Then we use this bundle as argument to a sum aggregation. The result will look like this:

As you see the total is again the one desired. What happened?

The aggr function in the individual rows will evaluate to only a single value. This is because the dimension only has one possible value on each ordinary data row. Since the inner dimension and expression are the same as for the surrounding chart, each value will be exactly the same as the result without the enclosing sum and aggr functions.

For the total row however, the aggr function will return three values, one for each value of the dimension field. These will in turn be summed by the sum aggregation. While formally still being an expression total, the result equals that of sum of rows.