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: 
richnorris
Creator II
Creator II

Percentage of top ten volumes?

I have a chart showing the regions with the top ten volume, showing the total volume for each of the top ten regions, and then the % of all total volume that this constitutes. I also want a total at the bottom. The trouble I'm having is that this % showing is coming out with a total of 100%, even though the regions not shown in the chart have some volume.

The way I am achieving the top ten, is by having an expression 'Rank'

if(rank(sum([Volume])) <=10, rank(sum([Volume])))

I am sorting on this, ascending, and on the presentation tab hiding the column, and setting max number to 10.

I have a dimension of Region,

to get the volume I simply summing volume,

and to get the percentage I am doing the following: sum([$(vCurrency) Volume]) / sum({1} TOTAL [$(vCurrency) Volume])

I am then setting total mode to Sum of rows.

Does anyone know why I am getting 100% for the total, when it should be closer to 60%?

thanks

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The max visible number approach won't work in this case. Instead, filter the top Region using a Calculated DImension.

=if(aggr(rank(sum([Volume])), Region) <=10, Region)

With that method, there is no need for the hidden Rank column or max visible.

-Rob

View solution in original post

4 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The max visible number approach won't work in this case. Instead, filter the top Region using a Calculated DImension.

=if(aggr(rank(sum([Volume])), Region) <=10, Region)

With that method, there is no need for the hidden Rank column or max visible.

-Rob

richnorris
Creator II
Creator II
Author

Thanks Rob thats Excactly what I needed!

richnorris
Creator II
Creator II
Author

Hmm, its not exactly right actually, because now there is a null value showing up in the list of regions, which represents all the regions outside of the top ten? Surely the whole point of setting a calculated dimension is to say, only show these things?

richnorris
Creator II
Creator II
Author

Oh, obviously just suppress when value is null for the dimension. Silly me, sorry. Thanks again Rob!