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: 
Not applicable

Custom dimension & custom sort

Community, I could really use your help.

I have a requirement for a Mekko chart, but the problem probably applies to many other situations as well.

First, in the chart I have to show the top 5 Company by Amounts, with all other companies placed in 'Others'. Ordinarily, I would just use the Dimension Limits option, but...

Secondly, any selections in Company (1 or more) have to be dynamically taken out of 'Others' and shown next to the top 5, and displayed at the bottom of the chart in a specific color. So the chart must be sorted: selections at bottom, top 5 in middle, 'Others' at top. Both the selections at the bottom, and the top 5, must be sorted by sum(Amounts).

I have a partial solution, but cannot figure out how to control the sorting. I have attached a qvw and data sample for you.

Currently, my custom dimension is the following:

=aggr(if(SubStringCount(Concat(Distinct GetFieldSelections(Company)), Company)>0,Company,

IF(RANK(SUM({<Company=>} Amounts)) <=5, Only({1}Company),'Others')),Company)

and my current sort expression sorts selections to the bottom, but can't control remaining values:

if(SubStringCount(Concat(Distinct GetFieldSelections(Company)), Company)>0,1e10,

sum({<Company=>}Amounts))

I really appreciate your help!

1 Solution

Accepted Solutions
Not applicable
Author

I managed to solve this. For anyone else who wants to control how 'Others' is sorted in a custom dimension, it's a great trick. In the sort expression, use if(count( {1} DISTINCT (Dimension),Dimension)>1, rank#. That will determine the rank order of 'Others', because it is the only value that has more than one of the original dimension in it.

View solution in original post

2 Replies
Not applicable
Author

Anyone have any ideas?

Basically what I am asking is, can I sort a dimension by two different criteria at the same time?

Not applicable
Author

I managed to solve this. For anyone else who wants to control how 'Others' is sorted in a custom dimension, it's a great trick. In the sort expression, use if(count( {1} DISTINCT (Dimension),Dimension)>1, rank#. That will determine the rank order of 'Others', because it is the only value that has more than one of the original dimension in it.