Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sort order with Class Function

Hi

I'm using the class function to create different bins for days. How do I sort it properly? i.e from the smallest '<0' to the largest '100+'?

The Chart shown below is sorted by Numeric Value Asc in the Sort tab.

Thanks

=if (DaysinPipe <0, '<0',
if (DaysinPipe =0,'0',
if (DaysinPipe<10,'1<= x <10',
if (DaysinPipe > 100,'100+',
Class(DaysinPipe,10)))))


Capture.PNG

1 Solution

Accepted Solutions
MarcoWedel

Dual() is a good advice in this case.

But using it on the Class() function like this will not change the result because class() already returns dual values.

A possible solution maybe could be:

=If(DaysinPipe <0, Dual('<0',-1),

    If(DaysinPipe =0,0,

      If(DaysinPipe<10,Dual('1<= x <10',1),

        If(DaysinPipe > 100,Dual('100+',100),

          Class(DaysinPipe,10)))))

hope this helps

regards

Marco

View solution in original post

6 Replies
sinanozdemir
Specialist III
Specialist III

You can use Dual() function, try something like the below:

=if (DaysinPipe <0, '<0',
if (DaysinPipe =0,'0',
if (DaysinPipe<10,'1<= x <10',
if (DaysinPipe > 100,'100+',
Dual(Class(DaysinPipe,10), Class(DaysinPipe,10))))))


and in the sort options, use Numeric sort.

Hope this helps.

Anonymous
Not applicable
Author

no... that doesn't work... the chart is sorting exactly the same way as in the screenshot....

sinanozdemir
Specialist III
Specialist III

Can you post a sample dataset?

Thanks

Anonymous
Not applicable
Author

Adding a sample data set.  

Opportunity IDDaysinPipe
0064000000aa1Xe98
0064000000aadlC202
0064000000aaFXI112
0064000000aafZs109
0064000000aAiCC121
0064000000aAjA7107
0064000000aAjwC112
0064000000aAkDw259
0064000000aAlAu174
0064000000aAlPu190
0064000000aaMl584
0064000000aAojH190
0064000000aAotV131
0064000000aApDH123
0064000000aaPNU143
0064000000aAppT190
0064000000aaxcu442
0064000000aay7S90
0064000000aayDp134
0064000000aB1KO136
0064000000aba6d248
0064000000abavg187
sinanozdemir
Specialist III
Specialist III

Please see the below screenshots:

Capture.PNG

And in the sort tab:

Capture2.PNG

Hope this is what you are looking for.

Thanks

MarcoWedel

Dual() is a good advice in this case.

But using it on the Class() function like this will not change the result because class() already returns dual values.

A possible solution maybe could be:

=If(DaysinPipe <0, Dual('<0',-1),

    If(DaysinPipe =0,0,

      If(DaysinPipe<10,Dual('1<= x <10',1),

        If(DaysinPipe > 100,Dual('100+',100),

          Class(DaysinPipe,10)))))

hope this helps

regards

Marco