Total in pivot is wrong

[Anonymous]

Hi,

I am quite new to QlikView and have a problem with a pivot table. I hope that you can help me with it.

Dimensions are Year, Quarter, Month

Expressions are FC1, FC2, FC3, Final

During the month I get values for FC1 then FC2 and so on.

I do not want to have a column for each expression but only 1 column which shows one of these expressions depending on whether there are values in or not. I did this with an  if formula in expressions like if(Final>0,sum(Final),if(FC3>0,sum(FC3),if(FC2>0,sum(FC2),if(FC1>0,sum(FC1),0.

It shows the right monthly values but the total of quarter ,year are wrong.

I attached a test file for you to better understand my issue. Thanks in advance for your help

regards,

Fredi

[sunny_talwar · Accepted solution]

Try this:

Sum(Aggr(if(

Final>0,sum(Final),

if(

FC3>0,sum(FC3),

if(

FC2>0,sum(FC2),

if(

FC1>0,sum(FC1),

0

)))), Year, Quarter, Month, Dpartment))

[Anonymous]

Works, thanks for your help