9 Replies Latest reply: Jan 30, 2018 11:04 AM by Sofia Vaz

# Sort Calculated dimensions 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 Calculated dimensions by expression

Hi

Using Sort Expression in property

```if([Markets] ='EUR',1
,if([Markets] ='USA',2
,if([Markets] ='INT ',3
,if([Markets] ='Others',4 ))))
```

Regards

An Pham

• ###### Re: Sort Calculated dimensions by expression

Hi An,

Thank you. I've tried that expression but QS doesn't recognize [Markets] as a dimension...

• ###### Re: Sort Calculated dimensions by expression

Hi

Please explain more detail or give sample data and expect output.

Regards

An Pham

• ###### Re: Sort Calculated dimensions by expression

What is your calculated measure dimension look like?

• ###### Re: Sort Calculated dimensions by expression

It looks like this:

=if(match(OrdMasterMarketCd, '10','11','12','13','14','15','17','22','23','24','25','28','29','93','94','93A','PK'), 'Europe',IF(match(OrdMasterMarketCd, '46'),'USA', if(match(OrdMasterMarketCd,'16','47','92','92Z','92A','92M','92L','92O', '16I'), 'International', 'Other')))

Thank You

• ###### Re: Sort Calculated dimensions by expression

Try this for sorting

Match(

If(Match(OrdMasterMarketCd, '10','11','12','13','14','15','17','22','23','24','25','28','29','93','94','93A','PK'), 'Europe',

If(Match(OrdMasterMarketCd, '46'),'USA',

If(Match(OrdMasterMarketCd,'16','47','92','92Z','92A','92M','92L','92O', '16I'), 'International', 'Other'))),

'Europe', 'USA', 'International', 'Other')

• ###### Re: Sort Calculated dimensions by expression

It has changed the order to USA, Other, Europe and International instead of 'Europe', 'USA', 'International', 'Other' ...

• ###### Re: Sort Calculated dimensions by expression

Try adding Only({1} .....) and see if that helps

Only({1}

Match(

If(Match(OrdMasterMarketCd, '10','11','12','13','14','15','17','22','23','24','25','28','29','93','94','93A','PK'), 'Europe',

If(Match(OrdMasterMarketCd, '46'),'USA',

If(Match(OrdMasterMarketCd,'16','47','92','92Z','92A','92M','92L','92O', '16I'), 'International', 'Other'))),

'Europe', 'USA', 'International', 'Other')

)

• ###### Re: Sort Calculated dimensions by expression

Yes Sunny, it's working now! Thank you very much!