Sort Calculated dimensions by expression

[Anonymous]

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

[kenphamvn]

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

[Anonymous]

Hi An,

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

[kenphamvn]

Hi

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

Regards

An Pham

[sunny_talwar]

What is your calculated measure dimension look like?

[Anonymous]

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

[sunny_talwar]

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')

[Anonymous]

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

[sunny_talwar · Accepted solution]

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')

)

[Anonymous]

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