Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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
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))