Custom Sort with Alphanumeric values

[Simon68]

Dear Fellows,

I am struggling to order some alphanumeric values that indicate tiers of customer spend. I want them to appear in the following order in a horizontal bar chart:

< zero
zero
>= 0 < 1,000
>= 1,000 < 5,000
>= 5,000 < 10,000
>= 10,000 < 20,000
>= 20,000 < 50,000
>= 50,000 < 100,000
>= 100,000 < 250,000
>= 250,000 < 500,000
nulls

I am using this Match expression in the sorting area:

Match(SpendTotal,'< zero','zero','>= 0 < 1,000','>= 1,000 < 5,000','>= 5,000 < 10,000','>= 10,000 < 20,000','>= 20,000 < 50,000','>= 50,000 < 100,000','>= 100,000 < 250,000','>= 250,000 < 500,000')

But when I choose Ascending I get these results:

zero
>= 5,000 < 10,000
>= 1,000 < 5,000
>= 0 < 1,000
< zero
>= 100,000 < 250,000
>= 10,000 < 20,000
>= 20,000 < 50,000
>= 50,000 < 100,000
>= 250,000 < 500,000
nulls

And with Descending I get these results:

>= 250,000 < 500,000
>= 5,000 < 10,000
>= 1,000 < 5,000
>= 0 < 1,000
< zero
>= 100,000 < 250,000
>= 10,000 < 20,000
>= 20,000 < 50,000
>= 50,000 < 100,000
zero
nulls

I guess it has something to do with the >= symbols and initial string value but I do not know how to overcome this.

I would be very grateful for any suggestions.

Kindest regards

Simon

[saurabhwadhwa]

Hi ,

Try with dual function. check out this post 

http://www.qlikfix.com/2010/12/27/creating-a-custom-sort-order-load-order-dual/

instead of creating a sort order field u can directly define the sorting order while creating the buckets .

BR,

Saurabh

[Simon68]

Thank you Saurabh,

Would this method work in Qlik Sense too?

I'm not sure how to apply the scripted preload to the Sorting expression.

Simon

[Simon68 · Accepted solution]

I eventually resolved this using the Sort Cards in the Data Manager.

https://help.qlik.com/en-US/sense-cloud/Subsystems/CloudHub/Content/Sense_Hub/LoadData/Customizing-t...