topic Sort by expression not working in App Development

Sort by expression not working

NVerma — Wed, 05 May 2021 10:46:37 GMT

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

Re: Sort by expression not working

eddie_wagt — Wed, 05 May 2021 11:29:43 GMT

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.

Re: Sort by expression not working

NVerma — Wed, 05 May 2021 12:07:51 GMT

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 ?

Re: Sort by expression not working

eddie_wagt — Wed, 05 May 2021 13:19:09 GMT

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

];