Sort Listbox which is grouped by if-expression

[#1]

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!

[tresesco]

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

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

[orital81]

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]

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 · Accepted solution]

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.

[#6]

Thank you very much for your quick response!

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

[#7]

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

[#8]

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

[#9]

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