topic Please help... SUM / AGGR in Pivot table is wrong!! in QlikView

Please help... SUM / AGGR in Pivot table is wrong!!

Tue, 22 Nov 2011 12:42:45 GMT

Hi everybody!

I have a question...

I created a pivot chart / table with a formula (I want to SUM() 3 different "elements" ("SB","SKY","MREC") and want to display the max one daily based.).

But if I'm checking the values the total sum (autogenerated) is WRONG !!! :

If I'm calculating (sum) the values manually (or in ms-excel), I will get another result:

I thinks this happens because the formula is still in this field while autogenerating the sum()-Line.

Is it possible to autogenerate the SUM() without using the formula in this field?

thanks,

jup

Please help... SUM / AGGR in Pivot table is wrong!!

swuehl — Tue, 22 Nov 2011 12:53:16 GMT

In a pivot, the total will be a total expression, not sum of rows as you might expect.

You will need advanced aggregation here, please check out "sum of rows in pivot tables" in the help file.

(Or post your complete expression so someone might help you on the syntax).

Regards,

Stefan

Please help... SUM / AGGR in Pivot table is wrong!!

Thu, 26 Jan 2012 15:18:47 GMT

Solved the problem. Take a look in the english manual (QlikView Reference Manual.pdf):

21.3 Sum of rows in Pivot Tables

I solved the problem using the aggr-function:

=sum(aggr(

if($(vSB) >= $(vSKY) and $(vSB) >= $(vMREC), $(vSB),

if($(vSKY) >= $(vSB) and $(vSKY) >= $(vMREC),$(vSKY),

if($(vMREC) > $(vSB) and $(vMREC) > $(vSKY), $(vMREC),

)

,DATE))