topic Re: Sort Listbox which is grouped by if-expression in QlikView

Sort Listbox which is grouped by if-expression

Wed, 21 Aug 2013 12:10:41 GMT

Dear Community,

I would be very thankful if someone could help me with the following issue:

I created a listbox for which I grouped the value with if-expressions,

so in the general tab I used the following expression;:

e.g.

=if([Revenue]>0 and [Revenue]<100,'less than 100', if([Revenue]>100 and [Revenue]<500,'less than 500, if([Revenue]>500 and [Revenue]<1000,'less than 1000'))

This gives the "Revenue"-listbox in the following form:

less than 100

less than 1000

less than 500

So how can I sort this listbox so the "less than 1000" comes after the "less than 500"?

Thank you very much!

Re: Sort Listbox which is grouped by if-expression

tresB — Wed, 21 Aug 2013 12:17:43 GMT

Use calculated sort(i.e, sort by expression). Expression:

Match('less than 100',less than 500','less than 1000')

Re: Sort Listbox which is grouped by if-expression

orital81 — Wed, 21 Aug 2013 12:23:29 GMT

If you sort by text it should sort correct since 500 is less than 1000 (500 in text value is smaller)

But you should have this field as the first sort field.

Re: Sort Listbox which is grouped by if-expression

abeyphilip — Wed, 21 Aug 2013 12:26:29 GMT

You can try this as well in your list box - sort expression:

=if([Revenue]>0 and [Revenue]<100,1,

if([Revenue]>100 and [Revenue]<500,2,

if([Revenue]>500 and [Revenue]<1000,3))

And you will have to select Ascending.

Re: Sort Listbox which is grouped by if-expression

swuehl — Wed, 21 Aug 2013 12:36:03 GMT

Or try creating dual values:

=if([Revenue]>0 and [Revenue]<100,

dual('less than 100',100),

if([Revenue]>100 and [Revenue]<500,

dual('less than 500',500),

if([Revenue]>500 and [Revenue]<1000,

dual('less than 1000',1000)

)

then sort by number.

Re: Sort Listbox which is grouped by if-expression

Wed, 21 Aug 2013 12:48:27 GMT

Thank you very much for your quick response!

I tried all your suggestions and the one of swuehl solved my problem!

Re: Sort Listbox which is grouped by if-expression

Wed, 21 Aug 2013 12:51:12 GMT

Unfotunately this doen't work since the 500 is not seen as a number but as a text and for that the "5" comes after the "1" even though it is followed by less zeros...

Re: Sort Listbox which is grouped by if-expression

Wed, 21 Aug 2013 12:53:15 GMT

This indeed changes the order, but not into the intended order, instead it still looks random??

Re: Sort Listbox which is grouped by if-expression

Wed, 21 Aug 2013 12:55:01 GMT

Thanks for the quick response...

I tried your suggestion but it doesnt changes the order at all, even though the expression sounds quite logic to me..