Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Wrong Total Sums in Pivot Table

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:

error loading image

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.

1 Solution

Accepted Solutions
Not applicable
Author

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))

View solution in original post

7 Replies
Not applicable
Author

Hi Moshea444,

I have solved it for you I think.

Can I send you the "new file"?

Regards,

Martijn

Not applicable
Author

you should be able to post it here right?

Not applicable
Author

Not applicable
Author

I will send a screenshot

Not applicable
Author

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

Not applicable
Author

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))

Not applicable
Author

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,

MT