Sort by expression not working

[NVerma]

I was trying to sort by expression for my dimension Diabetes Range having four value like Normal predicates,Diabetes and High diabetes using sort by expression 

if([Diabetes Range]='Normal',1,if( [Diabetes Range]='Prediabetes',2, if( [Diabetes Range]='Diabetes',3,if( [Diabetes Range]='High Diabetes',4 ) ) ) )

its working for line chart but not for bar chart . How to resolve

[eddie_wagt]

I tried to replicate your issue, but I get the expected result. It looks like it is sorting from high to low value. Can you share some data or app so I can test it with your datamodel or data? If you want always to sort in this order it is better to move this to the script.

[NVerma]

Hi Eddie,

Thanks for response

Actually i have one Dimension i.e Diabetes Range and measure i.e count(member id) so sorting for measure is default i.e auto and for Dimension i am using that if condition but not sure why its not working at my side. sorry for data sharing part as its client secure data cant share with you. 

I have also tried below logic to transfer this at load script side 

if(TEST_RSLT < 5.7, dual('Normal', 1)

,if(TEST_RSLT >= 5.7 and TEST_RSLT < 6.4 , dual( 'Prediabetes', 2)

,if(TEST_RSLT >= 6.4 and TEST_RSLT < 9, dual( 'Diabetes', 3)

,if(TEST_RSLT >= 9, dual('High Diabetes', 4)

)))) as "Diabetes Range"

so that i can short on numeric still not working . Is something default sorting is there which i am by mistake using ?

[eddie_wagt]

Hello @NVerma ,

I don't understand why it is not working for you, as I know there are no defaults in sorting otherwise than you have override this setting by sorting by expression. You have to sort on the dimension..

Otherwise try this:

Try to bring Diabetes Range to another table like this. 

YourOriginalTable:

LOAD ...

if(TEST_RSLT < 5.7,  1)

,if(TEST_RSLT >= 5.7 and TEST_RSLT < 6.4 ,  2)

,if(TEST_RSLT >= 6.4 and TEST_RSLT < 9, 3)

,if(TEST_RSLT >= 9,  4)

)))) as %DIABETES_RANGE_KEY

From --- 

Diabetes_range:

LOAD * INLINE [%DIABETES_RANGE_KEY, Diabetes Range

1, Normal

2, Prediabetes

3, Diabetes

4, High Diabetes

];