Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)?
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
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
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
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".
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
Thank you, it was indeed the missing sum aggr, what made my calculation doing wrong.
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!!
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