Custom Sort By Expression

[#1]

Hi. I have a dimension in a pivot table called Label.

The values of the labels are text strings. There are 5 of them.

'Very Big Label'

'Medium Big Label'

'Small Label'

'Default Label'

'Insignificant Label'

The people I am building the report for always want to see the labels in this order. I can't sort by Text, or Load order, or frequency, so I am wondering if I can create a custom expression to sort them in this order. But I am not very good with the expression language. Can anyone help me write an expression which will sort them in the correct way?

Thanks,

-JC

[prieper · Accepted solution]

Sort by the Result of the MATCH-function:

MATCH(CustomerRank, 'Very Big Label', 'Medium Big Label', 'Small Label', 'Default Label', 'Insignificant Label')

HTH
Peter

[#3]

I am trying to get this to work. In your formula what is CustomerRank?

[prieper]

This should be your fieldname for the grouping of the customers.

++
Peter

[#5]

The field name with these values is Label. Is that what you mean?

[prieper]

yes!

++
Peter

[#7]

Hi all,

this Match expression works great to sort my list box content being "red", "yellow", "green".

However, as soon as I select a value, this value is moved to the bottom of the list which is quite confusing for the user. How do I keep it from moving when I select?

[CELAMBARASAN]

Hi,

     In sort tab-->uncheck the sort by state option which makes possible or selected values to the top.

Celambarasan

[#9]

Hi,

somehow I got it to work, but I don't know how. The "sort by state" option is actually disabled at this moment. See below. Perhaps that is triggered by some other setting. But now I know what that option is doing - thanks 🙂