topic Re: Pivot Table Total does not add up in QlikView

Pivot Table Total does not add up

Wed, 01 Feb 2012 15:35:26 GMT

I have a if summum funtion

If(Rad_Nat_Code = 'None', Sum(Cost_Per_Unit),

Sum(Cost_Per_National_Unit)

But the total are not adding up at the bottom of the pivot table

can anyone help please

Re: Pivot Table Total does not add up

picturebox — Wed, 01 Feb 2012 15:46:38 GMT

Hi,
I just had the same problem... and I have the answer ... have a look at my post Problems with Pivot table totals from today ...
you need to use the aggr() function, buit it is all niely explained there.
Petra

Re: Pivot Table Total does not add up

Wed, 01 Feb 2012 15:55:23 GMT

Thanks but no luck

I have just changed it to

sum(aggr(if(Rad_Nat_Code = 'None', Sum(Cost_Per_Unit), Sum(Cost_Per_National_Unit)))

but now all my totals have gone !

Thanks for your help .

Pivot Table Total does not add up

swuehl — Wed, 01 Feb 2012 16:01:13 GMT

You need to add all your table dimensions to the aggr() function dimension list as parameter:

sum(

aggr(

if(Rad_Nat_Code = 'None', Sum(Cost_Per_Unit), Sum(Cost_Per_National_Unit))

, DIMENSION1, DIMENSION2

))

replace the DIMENSION1, DIMENSION2 with your dimensions.

field names are cases sensitive. And check for missing paranthesis.

Hope this helps,

Stefan

Re: Pivot Table Total does not add up

picturebox — Wed, 01 Feb 2012 16:05:05 GMT

Hi,
you need to add your dimensions to the function ... like this:

=sum(aggr(if (sum(leh_EU_YTD_CUR)=0, sum(Umsatz2011), sum(leh_EU_YTD_CUR)), Cat2, Cat4, dmListung, IRI_Bezeichnung))

where Cat2, Cat4 etc are the dimensions you want totalled and have set in the pivot. And there is a good section in the help, you need to just locate it... have another look see..

Petra

Re: Pivot Table Total does not add up

hic — Wed, 01 Feb 2012 16:12:24 GMT

The Aggr-function could work, but it is probably overkill.

Your expression will not work since the condition probably is undefined.

The condition Rad_Nat_Code = 'None' is not defined if there are several values of Rad_Nat_Code. You might want to try

Sum( If(Rad_Nat_Code = 'None', Cost_Per_Unit, Cost_Per_National_Unit))

instead.

Or use dimensions that ensure that Rad_Nat_Code never has more than one value.

Re: Pivot Table Total does not add up

Wed, 01 Feb 2012 16:13:38 GMT

Thanks adding the demisions worked