Sort dimensions with expression when dimensions created with aggr function

[Anonymous]

hi all,

I have the following bar chart. I want to sort the dimensions in a logical order (e.g. first "less than 5 dd", second "from 6 to 10 dd" and so on).
However, I used an AGGR function to create these dimensions, so if I try a custom sort with a MATCH function, it doesn't work. My guess is because these are not ad hoc fields in my DB.

 I also tried to put my AGGR function in my MATCH function like this: match(aggr(...),'less than ...','from 6 to 10dd', etc). It is not working either, so I'm a bit lost.

Do you have any suggestions?

I enclose the bar chart I'm using.

Thank you very much in advance!

[rwunderlich · Accepted solution]

Create your dimension values using the Dual() function. Assign the "less than 5 dd" as the Dual() string value and a sort order number as the num value.  Then sort your dimension column by numeric.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Anonymous · Accepted solution]

Hello,

Assuming you have logic in your dimension that evaluates a numeric value to create dimension groups such as this:

If(aggr(...)<x1,'less than 5 dd', If(aggr(...)<x2,'from 6 to 10 dd', ...))

If you apply a MAX function to the AGGR function, you should find that the entire group gets evaluated based on the largest value in that group, which will order it the way you want. So:

MAX(AGGR(...)) is all you'd need in your sorting expression.

Alternatively, if you're not evaluating a numeric value, try inserting the entire logic equation used for the dimension, but assign a value instead of a string:

e.g. If(match(aggr(...),...),1,If(match(aggr(...),...),2, ...))

You can now sort on this numeric value while the dimension is viewed as the desired string.

[Anonymous]

hi,

it seems that it is not working, I do have numeric values in my "age column" in the excel.

I applied your suggestion as follows:

=max(aggr(if([Comment age]>30,'over 30 dd',if([Comment age]<=30 and [Comment age]>20,'from 21 to 30 dd',if([Comment age]>10 and [Comment age]<=20,'from 11 to 20 dd',if([Comment age]>5 and [Comment age]<=10,'from 6 to 10 dd',if([Comment age]<=5,'less than 5 dd'))))),[Comment age]))

expression seems fine, however the bar chart doesn't sort.

[Anonymous]

Hi Rob, thanks for the answer.

I'd like to  investigate it more in case I need it in the future:

I tried to use the following formula in the "sorting" tab - to be sure I explained myself, I attach a snapshot. This attempt did not work.

dual('less than 5 dd',1) and dual('from 6 to 10 dd',2) and dual('from 11 to 20 dd',3) and dual ('from 21 to 30 dd',4) and dual('over 30 dd',5)

Then, I tried to put this same formula at the end of my aggr function in the dimension tab like this:

=aggr(if([Comment age]>30,'over 30 dd',if([Comment age]<=30 and [Comment age]>20,'from 21 to 30 dd',if([Comment age]>10 and [Comment age]<=20,'from 11 to 20 dd',if([Comment age]>5 and [Comment age]<=10,'from 6 to 10 dd',if([Comment age]<=5,'less than 5 dd'))))),[Comment age]) and dual('less than 5 dd',1) and dual('from 6 to 10 dd',2) and dual('from 11 to 20 dd',3) and dual ('from 21 to 30 dd',4) and dual('over 30 dd',5)

in this case, the bar chart goes blank 🙂

could you kindly elaborate a bit more? I'm still a new to Qlik formulas.

thanks again,

Alia

[Anonymous]

hi all,

I realized I made a mistake in the sorting tab, I was putting first the # of comments and second the clusters I created.

Now that I inverted the order, both of your suggestions work!

thank you again for your help.

Alia

[rwunderlich]

I was thinking you would define your dimension like this:

=aggr(
if([Comment age]>30,dual('over 30 dd',1)
,if([Comment age]>20,dual('from 21 to 30 dd',2)
,if([Comment age]>10,dual('from 11 to 20 dd',3)
,if([Comment age]>5, dual(from 6 to 10 dd',4)
,dual('less than 5 dd',5)))))
,[Comment age])

And then sort by Numeric.  No sort expression required. 

BTW, since you are testing your values high to low, there is no need for the "and" test.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[anandkamat]

This was really helpful. thank you..

[anandkamat]

This was helpful. Thank you!