Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Sort Listbox which is grouped by if-expression

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Sort Listbox which is grouped by if-expression

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.

View solution in original post

8 Replies
MVP
MVP

Re: Sort Listbox which is grouped by if-expression

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

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

Partner
Partner

Re: Sort Listbox which is grouped by if-expression

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.

abeyphilip
Contributor II

Re: Sort Listbox which is grouped by if-expression

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.

MVP
MVP

Re: Sort Listbox which is grouped by if-expression

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.

View solution in original post

Not applicable

Re: Sort Listbox which is grouped by if-expression

Thank you very much for your quick response!

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

Not applicable

Re: Sort Listbox which is grouped by if-expression

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

Not applicable

Re: Sort Listbox which is grouped by if-expression

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

Not applicable

Re: Sort Listbox which is grouped by if-expression

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