Custom dimension & custom sort

[#1]

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!

[#2]

Anyone have any ideas?

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

[#3 · Accepted solution]

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.