Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Partial Sum Totals Error in Pivot Table

Hi All,

I have created a pivot table and I'm using the partial sum total on one dimension from the presentation tab. The problem is the incorrect totals that are coming up. Any suggestions on how to correct the partial sum totals would really be appreciated.

Thank you in advance.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

So your first count should be 2660 and your second total 2609, right?

Have you tried the advanced aggregation, like

=sum( aggr( count( value), COLOR, STATUS))

View solution in original post

12 Replies
swuehl
MVP
MVP

Pivot tables will evaluate your partial sums as expression total, while you probably expect a sum of rows.

Please check the Help pages for 'Sum of rows in pivot tables' using advanced aggregation.

Your expression might need to be changed to something like

=sum( aggr( sum(Value), TableDimension1, TableDimension2, ...))

Hope this helps,

Stefan

Not applicable
Author

Hi Stefan,

Thanks for your reply. I have 2 expressions as follows:

EXP 1) count( value)

EXP 2) count(value)/count( value2)

I'm using 2 dimensions. Now, I'm using the partial sums on 1 dimension , just to get the total of my expression 1. So, basically in my pivot table I want to add a Total row showing the expression total.

I'm not sure where to use the sum expression you stated above.

thanks. I will really apprecite your reply.

swuehl
MVP
MVP

When you are saying, you want the expression total, I think you should already see it...

Could you post a screenshot of your table here and the values for the total you expect to see?

If you want the sum of rows (what I still assume), you need to use your expression, i.e. the count( value) within the aggr() function, and then a sum around all to get the sum of rows, like

=sum( aggr( count( value), dimension1, dimension2))

You need to replace dimension1, dimension2 by your actual dimension field names, of course.

Regards,

Stefan

Not applicable
Author

Hi Stefan,

This is a screenshot of the pivot table. As you could see , the count total in both the columns is showing a wrong total. It is not accurate. I need the total of all the columns at the bottom.

Untitled.png

Thank you. I really appreciate your prompt replies.

swuehl
MVP
MVP

So your first count should be 2660 and your second total 2609, right?

Have you tried the advanced aggregation, like

=sum( aggr( count( value), COLOR, STATUS))

Not applicable
Author

Yes, you are right. The totals should be 2660 and 2609. If you are saying that i should another expressions =sum( aggr( count( value), COLOR, STATUS)), then that would add another column to it. And I've tried it, but its still not working. Its displaying nothing.

thank you.

swuehl
MVP
MVP

Well you could and should use the expression instead of your original one (but for testing, having another one should bother).

Please check that the field names are correct and correctly spelled, also the case is correct (field names are case sensitive). Are your field called 'value', 'COLOR' and 'STATUS' ?

Could you upload a small sample here?

vijay_iitkgp
Partner - Specialist
Partner - Specialist

Hi , I think this is problem of Loosly coupled dimensions. Are you using dimensions and expressions from two different tables lnked on some key. If yes the please chek your key this key is not giving you 1:1 linking.

Regards

Vijay

v_iyyappan
Specialist
Specialist

Hi,

     I attached sample Test.qvw file. It may help for you.

Regards,

Iyyappan.