Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chriys1337
Creator III
Creator III

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

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)?

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

7 Replies
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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
marcus_sommer

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
Creator III
Creator III
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
chriys1337
Creator III
Creator III
Author

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

chriys1337
Creator III
Creator III
Author

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:

  

DimSum ASum BQuota
A250,400000000000
B601500,400000000000
Total621550,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