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: 
slribeiro
Partner - Creator
Partner - Creator

set analysis total by dimension

Hello.

I'm here with some issue in set analysis.

Imagine I want the following table.

ProductTypeCountrySalesTotal Sales by Country
1US9010000
1China15015000
1Australia305000
1Greece10040000
2China5015000
2Australia205000
2Greece20040000

To calculate Sales we use the regular sum({$<year={$(=max(year))}>} Sales), but I need help to calculate the Total Sales by Country.

The thing is to calculate something similar to sum({$<year={$(=max(year))}>} total Sales) but by country.

Thanks in advance.

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Sum(Aggr(Sum({1<year={$(=max(year))}>} TOTAL <countrydesc> Sales), TypeProduct, countrydesc))


Capture.PNG

or you can use this:

Sum(Aggr(Sum({<year={$(=max(year))}, TypeProduct>} TOTAL <countrydesc> Sales), TypeProduct, countrydesc))

View solution in original post

8 Replies
sunny_talwar

May be this:

Sum({$<year={$(=max(year))}>} TOTAL <Country> Sales)

slribeiro
Partner - Creator
Partner - Creator
Author

Yes, I've tried that and it works only if I use a straight table.

Do you have any ideia why that doesn't work in a pivot table?

best regards

sunny_talwar

It should still work, would you be able to share a sample where this isn't working in a pivot table?

slribeiro
Partner - Creator
Partner - Creator
Author

What happens is that it only works if you have your pivot table fully expanded.

I attached a sample I've just made for you.

sunny_talwar

What do you want your table to display when it isn't expanded?

Anonymous
Not applicable

hi try this

aggr(rangemax(above(sum(sales)),0,rowno())),country)......for country wise sales

or

rangemax(above( TOTAL sum(sales)),0,rowno(TOTAL))) for consolidated sales...

slribeiro
Partner - Creator
Partner - Creator
Author

Screenshot_1.jpg

I this first situation all the values are correct because the second column has the total of sales for each country, but if you reduce or select one TypeProduct your values will be wrong as the ideia would be to maintain the same values above

Screenshot_2.jpg

sunny_talwar

Try this:

Sum(Aggr(Sum({1<year={$(=max(year))}>} TOTAL <countrydesc> Sales), TypeProduct, countrydesc))


Capture.PNG

or you can use this:

Sum(Aggr(Sum({<year={$(=max(year))}, TypeProduct>} TOTAL <countrydesc> Sales), TypeProduct, countrydesc))