topic Pivot Table Column Total & Subtotal Problem in QlikView

Pivot Table Column Total & Subtotal Problem

Tue, 09 Aug 2011 09:52:12 GMT

Hi guys,

Having a pretty strange problem here. I have a pivot table with different expressions.

The expressions are as simple as sum(value), sum(price) and so on.

One of my expressions has a division, like sum(value * price)/sum(quantity).

For every other expression in my pivot table, the totals and sub totals are correct, but for this expression, the totals and sub totals are wrong.

Can someone help me with this please?

Thanks

Pivot Table Column Total & Subtotal Problem

swuehl — Tue, 09 Aug 2011 10:14:29 GMT

Hi,

the pivot table totals are always calculated as expression total (like putting the expression in a text box).

(for more details see also the chapter "nested aggregations and related issues" - "sum of rows in pivot tables" in the Help file)

To see what the exact problem in your case is, it would be best if you could post an example file demonstrating your issue.

Reagrds,

Stefan

Re: Pivot Table Column Total & Subtotal Problem

Tue, 09 Aug 2011 15:03:57 GMT

Hi,

I have attached a sample app to explain my problem.

My subtotals and overall total in the pivot table are wrong, as the pivot table for some reason does not sum the total and sub total values correctly.

Because I am using two groups, I tried using the aggr function, but couldn't get it to work properly.

Please I need help to get my total and sub total values right.

Thanks

Re: Pivot Table Column Total & Subtotal Problem

swuehl — Tue, 09 Aug 2011 15:30:00 GMT

Hmm,

if I look at your pivot with dimensions Country and Product Type I see:

Country	Product Type	Value	Weighted Value	Count
Canada	Furniture	£140.295	£731	192	730,7031
Canada	Office	£155.745	£811	192	811,1719
Canada	Total	£296.040	£771	384	770,9375
Mexico	Furniture	£93.427	£487	192	486,599
Mexico	Office	£106.639	£555	192	555,4115
Mexico	Total	£200.066	£521	384	521,0052
U.S.A.	Furniture	£457.179	£794	576	793,7129
U.S.A.	Office	£451.422	£784	576	783,718
U.S.A.	Total	£908.600	£789	1152	788,7155
Total		£1.404.706	£732	1920	731,6178

(I 've added last two columns to show count and average value calculated in Excel.

The Weighted Value seems ok to me, also in subtotals and total. For a given segment, the subtotal Wighted Value is of course not the average of the Weighted Values of the single rows.

Could you give an example of what you expect to see?

Regards,

Stefan

Re: Pivot Table Column Total & Subtotal Problem

Tue, 09 Aug 2011 21:32:42 GMT

Hi Stefan,

Thanks for your response. In the total line for say Canada. I don't want the total for the Weighted Value to be the average of 731 and 811. I want it to be a sum. I want it to just display the sum of (731+811). Just the same way the value column adds the values together for the total row.

I hope I have made myself clear enough.

Thank you

Re: Pivot Table Column Total & Subtotal Problem

swuehl — Tue, 09 Aug 2011 22:36:59 GMT

Ok,

I think this should work as expression:

=SUM(aggr(avg(Actual),[New Group],[New Group2]))

Just for my curiosity: how do you interprete/use the sum of average or weighted values?

Regards,

Stefan

edited by swuehl

Re: Pivot Table Column Total & Subtotal Problem

Wed, 10 Aug 2011 08:33:39 GMT

Thanks for all your help Stefan.

I don't really like weighted values as well, but my boss would like to use them in this application to determine the relative importance of each quantity on the average.

Kind Regards

Re: Pivot Table Column Total & Subtotal Problem

Mon, 30 Sep 2013 13:26:28 GMT

Hi Razor,

Even I faced such type of problem in my Pivot.... You can get perfect subtotal if you use :

=Sum(Aggr(.....Put your entire Expression here.....))

If still you didnt get correct total then let me know...

Regards,

Yojas