Expression: Total Mode in a Pivot, how does it work?

[chriys1337]

Hi,

I am using following Expression in a Pivot, and I am wondering how my Totals are calculated. Because under Tab "Expressions" the "Total Mode" is shown greyed out and set to "Total Expression".

if(sum(Min_Triggered_calc)<0,

sum([volume]) + sum(Min_Triggered_calc), sum([volume])*sum(Min_Triggered_calc))

The reason behind this formula is following:

If the field Min_Triggered_calc is smaller than 0, the value (+5 or +10) should be add to the field value volume, if it is bigger than 0, the value (0,9)should be multiplied with the value from the field volume.

In the Min_Triggered_calc field there are +5,10 OR 0,9 as possibility.

I am asking how the logic is due to the fact, that my total in this pivot seems to be corrupt. It is not summing up correctly. I guess its because of the OR logic (multiplicaton or summing up)?

[Anil_Babu_Samineni]

In pivot mode, the total mode is disabled

to get total, you can use like below

Sum(if(sum(Min_Triggered_calc)<0, sum([volume]) + sum(Min_Triggered_calc), sum([volume])*sum(Min_Triggered_calc)))

If not, What are the expressions for 5, 10 and 0,9

[marcus_sommer · Accepted solution]

Quite often you will need to wrap your calculation with an aggr-function to ensure that each calculation is performed on row-level and only their results are summed as totals. I mean something like this:

sum(aggr(

     YourExpression,

     Dim1, Dim2))

- Marcus

[chriys1337]

The field Min_Triggered_calc has following values: 5,10, 0,9

E.g. for Row 1 the value from the field Min_Triggered_calc is 5, therefore it should be summed with the value from sum(Volume).= 2+5=7

Row 2 the value is 0,9, therefore it should be multiplied with the value from sum(Volume) - 10*0,9= 9

I tried to put a sum() around my expression, but it gives me a "Error in Expression".

[Anil_Babu_Samineni]

My bad, You must use Aggr over sum like below in synthetically

Sum(Aggr(if(sum(Min_Triggered_calc)<0, sum(volume) + sum(Min_Triggered_calc), sum(volume)*sum(Min_Triggered_calc)),Your Pivot Dimensions))

But, Still not understand the question your intend over here

[chriys1337]

Thank you, it was indeed the missing sum aggr, what made my calculation doing wrong.

[chriys1337]

Thank you very much, this was the missing part, so now the pivot Totals are calculating correct. Is there any explanation, why this is needed. I would have thought before, that QlikView is doing this correct also without a sum(aggr()), but obviously not!!

[marcus_sommer]

With just simple sums the totals of the partial sums will work like expected but if the calculations are quotes or are depending / different to certain conditions it often doesn't work because each total will be calculated without any respect of the included dimension-values. I hope the following small example makes it clear what's happening:

Dim	Sum A	Sum B	Quota
A	2	5	0,400000000000
B	60	150	0,400000000000
Total	62	155	0,413333333333

From the quota of each row it could be assumed that the total showed the same 0,4 but qlik will calculate 62 / 155 which is then different (in the above case you would need to use avg(aggr(Expression, Dim)) to get the 0,4).

- Marcus