Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Custom dimension & custom sort

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.

2 Replies
Not applicable

Re: Custom dimension & custom sort

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

Re: Custom dimension & custom sort

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.

Community Browser