topic Subtotal wrong in Pivot Table when using IF in QlikView

Subtotal wrong in Pivot Table when using IF

annabergendal — Thu, 02 Oct 2014 12:50:54 GMT

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:

(

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

)

Anonymous — Thu, 02 Oct 2014 14:04:18 GMT

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 — Thu, 02 Oct 2014 14:32:00 GMT

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