Sort order with Class Function

[Anonymous]

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)))))

[sinanozdemir]

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]

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

[sinanozdemir]

Can you post a sample dataset?

Thanks

[Anonymous]

Adding a sample data set.  

Opportunity ID	DaysinPipe
0064000000aa1Xe	98
0064000000aadlC	202
0064000000aaFXI	112
0064000000aafZs	109
0064000000aAiCC	121
0064000000aAjA7	107
0064000000aAjwC	112
0064000000aAkDw	259
0064000000aAlAu	174
0064000000aAlPu	190
0064000000aaMl5	84
0064000000aAojH	190
0064000000aAotV	131
0064000000aApDH	123
0064000000aaPNU	143
0064000000aAppT	190
0064000000aaxcu	442
0064000000aay7S	90
0064000000aayDp	134
0064000000aB1KO	136
0064000000aba6d	248
0064000000abavg	187

[sinanozdemir]

Please see the below screenshots:

And in the sort tab:

Hope this is what you are looking for.

Thanks

[MarcoWedel · Accepted solution]

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