7 Replies Latest reply: Jun 21, 2018 5:38 AM by Sofia Vaz

# Sort a calculated dimension by Expression

Hi all,

I have two tables, each one with a calculated dimension and a measures.

One of the dimensions is called "Markets" and will divide my measure by EUR, USA, INT and Others. How can I define the order like that? What expression should I use? I've tried a lot of things but not've been succeded.

Thank you.

Sofia Vaz

• ###### Re: Sort a calculated dimension by Expression

Have u tried to use the sort tab and put ur measure above?

• ###### Re: Sort a calculated dimension by Expression

Yes. I've tried expressions like this one

if([Markets] ='Europe',1

,if([Markets] ='USA',2

,if([Markets] ='International ',3

,if([Markets] ='Others',4 ))))

in the sort tab but it doesn't recognize [Markets] as a dimension .

If I put the measure above I'll be sure that the dimension fields will be always as they appear now (by chance, in this case, if I do that it will sort the dimensions as I want, but in other cases it doesn't)

• ###### Re: Sort a calculated dimension by Expression

Use this,

if([Markets] ='Europe',Dual('Europe',1)

,if([Markets] ='USA',Dual('USA',2)

,if([Markets] ='International ',Dual('International',3)

,if([Markets] ='Others',Dual('Others',4)))))

And sort it numerically..!!

• ###### Re: Sort a calculated dimension by Expression

Can you explain it clearly?

Do you want to sort the dimension in a way EUR, USA, INT and Others?

• ###### Re: Sort a calculated dimension by Expression

Yes, that's it.

• ###### Re: Sort a calculated dimension by Expression

FoodSortOrder:

[Food, FoodSortOrder

Eggs,1

Bacon,2

Potatoes,5

Milk,6

Coffee,7];

then in the Chart, Sort Tab, click the Dimension you want the sort to happen - in this case Food

check the sort by expression

and key FoodSortOrder in the expression field.

it will sort in the numeric order that you defined

• ###### Re: Sort a calculated dimension by Expression

Thank you Sue. Unfortunately I'm not allowed to edit the scrip. I've solved the problem with Mohan expression.

thanks!

Sofia Vaz