Sorting Straight Table column

[garyvary123]

Hi guys, I am trying to sort a column based on an expression I made for the dimension.

For ex:

=if([Num] < 500, '<500',
if([Num] >= 500 and [Num] < 1000, '500-1000',
if([Num] >= 1000 , '>=1000')))

How would I specify the sort order in the Sorting tab for this? I can't seem to get it. Thank you.

[brunobertels]

Hi

not sure for not tested but try this 

Sort by expression 

=if([Num] < 500,1,
if([Num] >= 500 and [Num] < 1000, 2,
if([Num] >= 1000 , 3)))

Regards 

[garyvary123]

Thanks for your reply but this does not work.

[brunobertels]

Hi 

what about this :

=if([Num] < 500, '<500',
if([Num] >= 500 and [Num] < 1000, '500-1000',
if([Num] >= 1000 , '>=1000',class([Num],500))))

or this : 

=if([Num] < 500, dual('<500',1),
if([Num] >= 500 and [Num] < 1000, dual('500-1000',2),
if([Num] >= 1000 , dual('>=1000',3))))

and order by number ascending 

[garyvary123]

Hey, thank you again for your response. I tried both of these and still it's not working.

[sasikanth]

HI Gary, 

Option1: 

"Sort alphabetically" option would work in the case of calculated dims, 

Option2:

use below expression in "Sort by expression" 

=Match( (if([Num] < 500, '<500',
if([Num] >= 500 and [Num] < 1000, '500-1000',
if([Num] >= 1000 , '>=1000'))) ),' <500','500-1000','>=1000')

OR calculate Dim at script level and use below expression

=Match( Num_script_cal , ' <500', '500-1000', '>=1000')

Thanks, 

sasi