topic Wrong Total Sums in Pivot Table in QlikView

Wrong Total Sums in Pivot Table

Thu, 03 Feb 2011 16:50:05 GMT

I have a pivot table with two columns called Sales and Americas Take. If a sale happens outside of the Americas there is still a portion of the sale that is considered part of Americas. This is what the Americas Take number is. Here's a screen shot:

You can see that the totals 215 + 550 + 500 do NOT equal 1350. The graph is showing 1350 because Americas sales is 300 before taking out the Americas Take number (85 in this case)

Here's the Sales column equation:

=if(RegionName='America', sum(SaleAmount)-sum(Total TakeAmount), sum(SaleAmount))

I'm trying to get the sum to show 1265. I have tried the following with no luck:

=if(RegionName='America', sum(aggr(sum(SaleAmount)-sum(Total TakeAmount), RegionName)), sum(SaleAmount))

Any thoughts? Thanks. Attached is the file.

Wrong Total Sums in Pivot Table

Thu, 03 Feb 2011 17:14:07 GMT

Hi Moshea444,

I have solved it for you I think.

Can I send you the "new file"?

Regards,

Martijn

Wrong Total Sums in Pivot Table

Thu, 03 Feb 2011 17:18:02 GMT

you should be able to post it here right?

Wrong Total Sums in Pivot Table

Thu, 03 Feb 2011 17:19:22 GMT

Wrong Total Sums in Pivot Table

Thu, 03 Feb 2011 17:20:00 GMT

I will send a screenshot

Wrong Total Sums in Pivot Table

Thu, 03 Feb 2011 17:21:47 GMT

thanks, but I still want the Sales column to show 215 for america

Wrong Total Sums in Pivot Table

Thu, 03 Feb 2011 17:37:26 GMT

Nevermind I got it. You have to do aggr around the whole IF:

=sum(aggr(if(RegionName='America', sum(SaleAmount)-sum(Total TakeAmount), sum(SaleAmount)), LineName,RegionName))

Re: Wrong Total Sums in Pivot Table

Mon, 22 Oct 2012 08:33:55 GMT

are LineName and RegionName some fields? I have kind of the same problem but I'm not able to solve it with the aggr function cause I don't really understand how it works

regards,