How to use interactive sorting based on an expression

[Anonymous]

Hi,

We have a column called rank which we are showing in a straight table chart, this is returned from the datasource as a string e.g. 1/50. When you use interactive sorting Qlikview will sort this column alphabetically e.g. 1/50, 10/50, 11/50, 2/50 etc. I tried to change the sort options for this column but the "Sort by Expression" option was greyed out, my thinking being that I could use a formula that only returns the left hand side of the rank string as number and then sort by that instead.

Has anyone got any suggestions of what we could do to get round this issue?

Thanks,

Ryan

[jvitantonio]

Hi,

There's a check box you need to check before using the Sort by expression option. also, this needs to be done in the dimension.

If you can't make this work, please attach your application.

You can also use your own Rank() expression to avoid problems wit this text field. Using right/left functions is not advisable  as you don't know the lenght of the strings. Ex: 1/50, 50/50, 2/125. They will all return different values. Although you could yo usomething like max(subfield(myRankField, '/'). This create 2 fields, one for the first part before "/" and another one for the second part after "/", you can then use max() to get the first value.

I hope this helps.

JV

BI Experience | A place to share our Business Intelligence experiences

[Anonymous]

not sure if i understand your issue. As you suggest, you can as well use the sort by expression to fix the sorting. are you not able to check this option also.

[#4]

Please try to change the numeric value in Sort. Right Click on sheet object -> Sort -> Click on expression->

Check Numeric Value and Text -> You could sort here.

Thanks,

Sai