# Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee

## Totals in Charts

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

Luminary

Hi Henric,

As always a great post.

I have a question; My understanding is aggr() has a larger calculation overhead that a simple sum().

Would you advise always use aggr() or, as I do, use aggr when required?

For me this may be using averages on the charts line and don't want to show a true average on the whole data set in the total.

I guess this depends on your data and your requirements. For me this is something to always have in mind when developing although 99% of the time a basic sum() type expression will give me the correct results.

Richard

QlikCentral.com

2,048 Views
Valued Contributor III

Good to be aware of this. As I wasn't when initially using QlikView.

An example I encountered was for  a service business.

I was required to show the distinct job count (distinct Call_Num) done over each month and year. The months did not add to the year (as a small number of jobs were started one month/year and finished the next). I was sort of Ok with this as the reason was clear and the difference was small. But a new manager was initially convinced my report was inaccurate (as I was at first).

if I was doing it again I would only include jobs finished for a year or month. As this report column just does not seem right (when the total is not the sum of the individual rows) even though the difference was small.

2,048 Views
Employee

Richard

Aggr() does indeed have a larger calculation overhead, since it implies a two-level, nested aggregation, whereas a simple Sum() is a single, faster aggregation. So my advice would be not to use Aggr(), unless absolutely necessary.

HIC

2,048 Views
Luminary

Cheers

Richard

2,048 Views
Contributor

Ohh !

Greaty Suggestion !

2,048 Views
Partner

hi HIC,

please suggest me for the below one

am having on issue, i have two date columns Transaction date and Duedate,

my report is calculated using Start and End date,

the following expression works, if date is present as dimension,

sum(amount)*(0.06)/360*(30-day(duedate)+1)

+

sum(amount)*(0.06)/12*((floor(End-DueDate)/30)-1)

+

sum(amount)*(0.06)/360*(day(End)-1)

if date is removed from dimension, the above calculation not works,

30-day(duedate)+1 is calculated in script, so no problem when i use field,

day(End)-1 is calculated in script, so no problem

(floor((End-Duedate))/30)-1 is not calculated in script, because, End is date, and Duedate is date but from different table.

i need the report as summary level not as a detailed...

could you please suggest some idea.

Thanks,

Kalyan.D

2,048 Views
Employee

Most likely, a specific row in the fact table (where "amount" is) can only have one "DueDate" and one "End" associated with it. If so, you should move your calculation into the Sum function:

0.06* sum(amount *

(

1/360*(30-Day(DueDate)+1) +

1/12*((Floor(End-DueDate)/30)-1) +

1/360*(Day(End)-1)

)

)

HIC

2,048 Views
Partner

HIC,

End date is taken from Logical island(Master Calendar), its not associated with fact table.

Best Regards,

Kalyan.D

2,048 Views
Employee

Then the formula I suggested should work - as soon as you have a single End date selected.

HIC

2,048 Views
Partner

HIC,

Thank you very much,

Its working fine.

Once again I thank you.

Best Regards,

Kalyan.D

2,048 Views
Esteemed Contributor III

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

2,048 Views
Not applicable

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

2,048 Views
Employee

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

2,048 Views
Valued Contributor

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

2,048 Views
Not applicable

Hello

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

Thank you HIC

2,048 Views
Employee

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

2,048 Views
Not applicable

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

2,048 Views
Contributor II

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.

2,048 Views
Employee

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.

2,048 Views
Contributor II

I got it. Thank you Henric.

2,048 Views
Contributor

Hi,

I am stuck with the same Issue where i have Dimension and the count of Customers for those Corresponding Dimension.

I have one single dimension and I am using the expression as if(Sum(Sales)>0,Count(Distinct Customer)) this gives me the correct data as per the line items i.e (386) but the total comes to (457), I am really confused and would really appreciate if you could help me out with this.

hic

Regards,

Saniya.

2,048 Views
Honored Contributor

Thanks a lot for this topic, that helps me, to solve my issue

2,048 Views
New Contributor III

Hi Henric,

I am looking for an aggregate expression to use to sort values of the sub dimensions of a pivot table in Qlik Sense. Find attached what I get when i try to sort by the plain expression. Somehow Qlik Sense does not work with subtotal and it sort the second dimension by its total not by the sub subtotal for each value of dimension 1.

2,048 Views
Employee

Congrats @hic, as usual you are enlightening.

A minor remark, you use Qlikview into your text, but it is applicable to both Qlikview an Sense, right?

I´m translating it into Portugues soon.

2,048 Views
Employee

Portuguese translation here

2,048 Views
Employee

Yes, it is just as applicable to Qlik Sense. 🙂

2,048 Views
New Contributor III

hi

in  Qlik sense i  facing  one  pbm  in  table  showing  total  value correct  but  kpi  value  showing  wrong  value  like

total  50

a     25

b      25

kpi  value  showing  in  25  it

2,048 Views
New Contributor

Hello Henric,

I have an issue with a pareto chart that uses a dimension that is like a sub category to a category. The sub category exists in another table that does not contain key. The sum Total does not work correctly for all selections. So I tried using below expression (read from your post)

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

When I display in text object it shows correctly but cumulative % on the chart for some reason does not sum up to 100% for all selections.

Cumulative % = RangeSum(Above(Sum(Qty), 0, RowNo())) / \$(vTotal)

where \$(vTotal) = Sum(Aggr( Sum(Qty), [sub category]))

176 Views
Employee

You write that the chart has "a dimension that is like a sub category to a category". Does this mean that the chart has two dimensions?

What do you mean by "The sub category exists in another table that does not contain key"? Is the sub category not linked to Qty at all? If it isn't, then it is not possible to make a Pareto chart.

You write that "sum total does nto work correctly for all selections". In what way is it wrong?

Totals are not necessarily the sum of the rows. Instead, it is the same calculation made over all data records, not just a subset of records. See for instance the example in the post above, where product can belong to two different product groups, so the total is smaller than the sum of the individual product groups. As it should be.

HIC

142 Views
New Contributor

I have a main fact table "Complaints" that has the qty measure. Within the fact table there is a "Reason" field (which will be used as a dimension on some charts). There is also a "Reason Detail" which exists in another table linked through an id field. The "Reason Detail" can have multiple rows for the same Complaint and Part Number.

The pareto chart by "Reason" works just fine but the pareto by "Reason Detail" does not work correctly (Sum Total did not work and I also tried sum(aggr(sum()) but nothing worked and I figured it is because of the one-to-many relationship between the fact and this dimension. So now I created another Fact (similar to the "Complaints" one using inner join with the Detail dimension and I think now things are working correctly.

125 Views