Sorting for calculated dimension

[ferha_jafri]

Hello Experts,

I have a calulated dimension as below which I wanted to sort in a particular sort order 

Expression :

=if( aggr(sum(Sales),Admin_ID)/1000000>=100 and aggr(sum(Sales),Admin_ID)/1000000<=500,'100M - 500M',

IF(aggr(sum(Sales),Admin_ID)/1000000>=50 and aggr(sum(Sales),Admin_ID)/1000000<100,'50M-99.9M',

IF(aggr(sum(Sales),Admin_ID)/1000000>=25 and aggr(sum(Sales),Admin_ID)/1000000<50,'25M-49.9M',

IF(aggr(sum(Sales),Admin_ID)/1000000>=10 and aggr(sum(Sales),Admin_ID)/1000000<25,'10M-24.9M')
)

)
)

Sort otder I want is same as in Expression, but not sure how to write an expression for sorting.

Thanks in advance.

[brunobertels · Accepted solution]

hi 

May  be replace value by a dual value using dual function : 

rather than 

'100M - 500M', use  dual('100M - 500M',1) 

'50M-99.9M', dual('50M-99.9M',2)

'25M-49.9M', dual('25M-49.9M',3)

'10M-24.9M' dual('10M-24.9M',4) 

[rwunderlich · Accepted solution]

Create your values using the Dual function, using the sort order as the numeric parm. Example

Dual('100M - 500M', 1)
Dual('50M-99.9M', 2)

Then set your chart Sort option to Numeric.

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