topic Total sum in Pivot in QlikView

Total sum in Pivot

Thu, 23 Jul 2009 03:12:51 GMT

Hi, I've been reading the forum all night long, now I'm tired so maybe someone can help me out with this pivot table. I can't get the total sum to calculate right. The sum should be 304065.

I Use following code to do the calculation. I tried to use the aggr function but I get the same result 😞 I would be more than happy if someone could give me a hint. Thanks!

=IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue]))

Total sum in Pivot

Thu, 23 Jul 2009 03:24:16 GMT

Try changing your calculation:

from this:

IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue]))

To this:

Sum(If([Curr] >"", [Curr]*[Unddel_ordervalue],[Unddel_ordervalue]))

-Ed

Total sum in Pivot

Thu, 23 Jul 2009 03:53:17 GMT

Unfortunatly, that was not the solution, I think it has to be someting with the aggr function....

Well the result from your solution (depending if I use '' or "":

Thanks anyway!

Total sum in Pivot

Anonymous — Thu, 23 Jul 2009 09:12:06 GMT

Hi danne31,

There is a chapter on this in the reference manual. "Sum of rows in a pivot table" or something like that in the back somewhere.

The if statement in the expression can make the dimension independent total be something else than the sum of rows. Still correct, but not what one would like to see. In that case you can aggregate the sums for each dimension value and then sum these up for the total.

if the original expression was: =IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue]))

You should be able to write: =sum(aggr(IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue])), Kund_beskrivning))

However, I see that you have more than one dimension hidden under the pivot expansion icons so you might want to add these dimension fields to the aggr function. This is all better explained in the reference manual so I suggest you look up the chapter I'm referring to.

cheers,

Johannes

Total sum in Pivot

Thu, 23 Jul 2009 18:07:43 GMT

Hi Johannes!

Perfect! Thanks a lot, it works just the way i want to!

/Daniel

Total sum in Pivot

Wed, 31 Mar 2010 10:37:09 GMT

Thank you Johannes

I have been pulling my hair out over this for almost 10 hours straight.