Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot Table Column Total & Subtotal Problem

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

7 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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

swuehl
MVP
MVP

Hmm,

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

CountryProduct TypeValueWeighted ValueCount
CanadaFurniture£140.295£731192730,7031
CanadaOffice£155.745£811192811,1719
CanadaTotal£296.040£771384770,9375
MexicoFurniture£93.427£487192486,599
MexicoOffice£106.639£555192555,4115
MexicoTotal£200.066£521384521,0052
U.S.A.Furniture£457.179£794576793,7129
U.S.A.Office£451.422£784576783,718
U.S.A.Total£908.600£7891152788,7155
Total £1.404.706£7321920731,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

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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