Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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
tresesco
MVP
MVP

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

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

orital81
Partner - Creator III
Partner - Creator III

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
Creator II
Creator II

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.

swuehl
MVP
MVP

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.

Not applicable
Author

Thank you very much for your quick response!

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

Not applicable
Author

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
Author

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

Not applicable
Author

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