
Re: Expression: Total Mode in a Pivot, how does it work?
Anil Babu Jun 28, 2017 8:04 AM (in response to Christian Schmitz)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

Re: Expression: Total Mode in a Pivot, how does it work?
Christian Schmitz Jun 28, 2017 8:22 AM (in response to Anil Babu )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".

Re: Expression: Total Mode in a Pivot, how does it work?
Anil Babu Jun 28, 2017 8:29 AM (in response to Christian Schmitz)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

Re: Expression: Total Mode in a Pivot, how does it work?
Christian Schmitz Jun 28, 2017 8:57 AM (in response to Anil Babu )Thank you, it was indeed the missing sum aggr, what made my calculation doing wrong.




Re: Expression: Total Mode in a Pivot, how does it work?
Marcus Sommer Jun 28, 2017 8:18 AM (in response to Christian Schmitz)Quite often you will need to wrap your calculation with an aggrfunction to ensure that each calculation is performed on rowlevel and only their results are summed as totals. I mean something like this:
sum(aggr(
YourExpression,
Dim1, Dim2))
 Marcus

Re: Expression: Total Mode in a Pivot, how does it work?
Christian Schmitz Jun 28, 2017 8:58 AM (in response to Marcus Sommer )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!!

Re: Expression: Total Mode in a Pivot, how does it work?
Marcus Sommer Jun 28, 2017 9:18 AM (in response to Christian Schmitz)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 dimensionvalues. 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

