topic Re: Sort order with Class Function in QlikView

Sort order with Class Function

Anonymous — Mon, 06 Jul 2015 21:09:40 GMT

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

Re: Sort order with Class Function

sinanozdemir — Mon, 06 Jul 2015 21:16:54 GMT

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.

Re: Sort order with Class Function

Anonymous — Mon, 06 Jul 2015 21:19:26 GMT

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

Re: Sort order with Class Function

sinanozdemir — Mon, 06 Jul 2015 21:20:44 GMT

Can you post a sample dataset?

Thanks

Re: Sort order with Class Function

Anonymous — Mon, 06 Jul 2015 21:25:04 GMT

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

Re: Sort order with Class Function

sinanozdemir — Mon, 06 Jul 2015 21:32:27 GMT

Please see the below screenshots:

And in the sort tab:

Hope this is what you are looking for.

Thanks

Re: Sort order with Class Function

MarcoWedel — Mon, 06 Jul 2015 21:56:15 GMT

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