Totals in Charts

[hic]

The total in a chart is not the sum of the individual rows of the chart.

Instead, the total and the subtotals are calculated using the expression – but on a larger subset of the data than for the individual row.

Usually, the two methods result in the same numbers, but sometimes there is a huge difference. One example of this is if you use a non-linear function, e.g. Count(distinct …) as expression. The example below clearly shows this.

The source data to the left assigns a country to each state, and if you count the number of countries per state using a Count(distinct Country), you will get the chart to the right: Each state belongs to one country only, and the total number of countries is 2, also if the chart has four rows.

A second example is if you have a many-to-many relationship in the data. In the example below, you have three products, each with a sales amount. But since each product can belong to several product groups, the sales amounts per product group will not add up: The total will be smaller than the sum of the individual rows, since there is an overlap between the product groups. The summation will be made in the fact table.

Another way to describe it would be to say that a specific dollar belongs to both product groups, and would be counted twice if you just summed the rows.

In both cases, QlikView will show the correct number, given the data. To sum the rows would be incorrect.

So, how does this affect you as an application developer?

Normally not very much. But it is good to be aware of it, and I would suggest the following:

• When you write your expression, you should have the total line in mind. Usually, the expression will automatically be right also for the individual rows.
• Always use an aggregation function. This will ensure that QlikView is able to calculate the total correctly.
• If you want an average on the total line, you should most likely divide your expression with Count(distinct <Dim>). Then it will work both for the individual rows (where the count is 1) and the total lines. Example

          Sum( Amount ) / Count( distinct Customer )

• For cases where you want to show something completely different in the total line, you should consider the Dimensionality() function, that returns 0, 1, 2, … depending on whether the evaluation takes place in a total, subtotal or row. Example:

          If( Dimensionality() = 0, <Total line expression>, <Individual line expression> )

But If I want to show the sum of the individual rows? I don’t want the expression to be calculated over a larger data set. What do I do then?

There are two ways to do this. First, you can use an Aggr() function as expression:

          Sum( Aggr( <Original expression> , <Dimension> ) )

This will work in all objects. Further, if you have a straight table, you have a setting on the Expressions tab where you can specify the Total mode.

Setting this to Sum of Rows will change the chart behavior to show exactly this: The sum of the rows.

HIC

[Anonymous]

Richard,
Henric said "always use an aggregation function", not "always use the aggr() function".  Aggr is not an aggregation function.  Aggregation functions examples:
count

sum

avg

min

max

[#3]

Thank you for this blog, I want to do  something similar but on the other way around, I want to  have a total according to distinct values of a dimensions like this picture shows

Thank you in advance

[hic]

I assume that you also have a third dimension in the chart - otherwise you wouldn't get multiple rows of A & A1...

If the "value" is found in the same data table as "sub category" (and this is the primary key) you will get the wanted behaviour automatically.

If not, you will have to create it using Aggr(), e.g.

     Sum(Aggr( Sum(value), [sub category] ))

HIC

[Anonymous]

Good post!

I would even like to see some advance summation concept in you future blogs. For example, how the Totals working if the column is calculated based on two different columns. How to handle the total if the column has negative numbers and it is derived based on other columns in the table etc.

Cheers

[#6]

Hello

Sorry for the very late reply, actually it worked with   Sum(Aggr( value, [sub category],category )).

Thank you HIC

[hic]

If you have exactly one value per [sub category], then your formula will work. But if there is the remotest chance that you might have several, you should instead use

     Sum(Aggr( Sum(value), [sub category],category )) , or

     Sum(Aggr( Avg(value), [sub category],category ))

Se more on

http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations 

http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/16/use-the-aggregation-functions

HIC

[#8]

Yes I have only one value per "sub category" .

[arethaking]

Hi hic , What is the meaning of this? Please explain.

Instead, the total and the subtotals are calculated using the expression – but on a larger subset of the data than for the individual row.

[hic]

Oh, this is very simple: It is just the matter of summing correctly if you have many-to-many relationships. For example, say that you have the following source data:

There are two books, each with a sales number. Further, each book can have one or several authors. Now, if you want to sum the sales, you may create the following pivot table:

This shows the correct sales number of each of the individual lines. The problem occurs when you want to create the totals line. If you just would sum the individual rows in the pivot table, you would get 400 $, which is incorrect. Instead, the engine calculates Sum(Sales) on the total sample - irrespective of the dimensional values - and finds 300 $, which is the correct answer.

[arethaking]

I got it. Thank you Henric.