Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Phan_Anh_Son
Partner - Contributor III
Partner - Contributor III

Wrong Total Sums in Pivot Table

I have a pivot table with two columns called Sales and Cost. Profit = Sales - Cost but still not working. What I am doing wrong?

Here's a screen shot:

Phan_Anh_Son_0-1719234981162.png

 

Labels (2)
2 Replies
marcus_sommer

The calculation itself is correct. That you didn't get the wanted result is caused from your condition which isn't fulfilled on the total-level and considering the Cost of 50 for Sales3.

You could adjust it by wrapping the calculation with an aggr() to enforce a summing against the row-level. This may look like: 

sum(aggr(rangemax(sum(Sales)-sum(Cost), 0), RegionName, LineName))

and to enable the logic against the dimension-groups you need to query the active group. This means for example replacing the dimension of RegionName with:

[$(=getcurrentfield(YourGroupName))]

Phan_Anh_Son
Partner - Contributor III
Partner - Contributor III
Author

Works, thanks for your help