Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
annabergendal
Partner - Creator
Partner - Creator

Subtotal wrong in Pivot Table when using IF

When I add an if-statement to my expression the subtotal gets wrong... How can I correct this?

I have tried with distinct which I read somewhere that you could use, and also suppress zeros for dimensions, but it did not work, please advice!

My expression:

If

(

Lagernr = 'A01' or Lagernr = 'A02' or Lagernr = 'AR1' or Lagernr = 'AB1' or Lagernr = 'AW1' or Lagernr = 'A03',

Sum(distinct(Lagersaldo)),

Sum(distinct(Lagersaldo)) + sum(DISTINCT(Ordered))

)

1 Solution

Accepted Solutions
Anonymous
Not applicable

Try this (I cleaned your expression a little, and added sum(aggr()) around it):

sum(aggr(

if(

match(Lagernr,'A01','A02','AR1','AB1','AW1','A03'),

sum(Lagersaldo),

sum(Lagersaldo)+sum(Ordered)

)

,Lagernr))

View solution in original post

2 Replies
Anonymous
Not applicable

Try this (I cleaned your expression a little, and added sum(aggr()) around it):

sum(aggr(

if(

match(Lagernr,'A01','A02','AR1','AB1','AW1','A03'),

sum(Lagersaldo),

sum(Lagersaldo)+sum(Ordered)

)

,Lagernr))

annabergendal
Partner - Creator
Partner - Creator
Author

Thanks! Together with all the rest dimennsions in the aggr funktion it works... Great