Sort custom dimension with multiple criteria

Tue, 14 Jul 2015 00:27:54 GMT

Community, I would love your help.

I have a complicated chart requirement. This is for a Mekko chart, but the problem applies to other types as well.

First, I have to show the top 5 in dimension 'Company' by sum(Amounts), regardless of selection, along with everything else lumped into 'Others'. However, I cannot just use Dimension Limits, because...

Secondly, if the user makes a selection, the selection(s) from Company must be added to that top 5. Furthermore, the selections must be sorted to the bottom, while the top 5 must remain sorted by sum(Amounts). 'Others' must remain at the top as well.

Currently, I am using a custom dimension:

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

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

This is letting me break out my selections from 'Others', however, I cannot figure out the sort expression.

How do I keep Others on top, my selections at the bottom, and top 5 in the middle sorted by Amounts? I have attached a data and chart sample.

Ideally, if you selected companies E and F, I would want the order in the table and chart to be:

Others, A, J, H, I, C, E, F

Thank you!

Re: Sort custom dimension with multiple criteria

Thu, 23 Jul 2015 13:14:13 GMT

Answered my own question over here: Custom dimension & custom sort

topic Re: Sort custom dimension with multiple criteria in QlikView

Sort custom dimension with multiple criteria

Re: Sort custom dimension with multiple criteria