topic Sort Calculated dimensions by expression in App Development

Sort Calculated dimensions by expression

Anonymous — Tue, 30 Jan 2018 09:44:04 GMT

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

kenphamvn — Tue, 30 Jan 2018 10:08:15 GMT

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

Anonymous — Tue, 30 Jan 2018 10:20:34 GMT

Hi An,

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

Re: Sort Calculated dimensions by expression

kenphamvn — Tue, 30 Jan 2018 10:26:15 GMT

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

Regards

An Pham

Re: Sort Calculated dimensions by expression

sunny_talwar — Tue, 30 Jan 2018 13:13:32 GMT

What is your calculated measure dimension look like?

Re: Sort Calculated dimensions by expression

Anonymous — Tue, 30 Jan 2018 14:15:36 GMT

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

sunny_talwar — Tue, 30 Jan 2018 14:25:22 GMT

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

Anonymous — Tue, 30 Jan 2018 14:57:02 GMT

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

Re: Sort Calculated dimensions by expression

sunny_talwar — Tue, 30 Jan 2018 15:02:53 GMT

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

Anonymous — Tue, 30 Jan 2018 16:04:09 GMT

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

Re: Sort Calculated dimensions by expression

smgt90 — Fri, 06 Dec 2019 21:00:02 GMT

Thanks! this helped me a lot

Re: Sort Calculated dimensions by expression

xavi_arm12 — Mon, 11 Apr 2022 06:18:37 GMT

Hi @sunny_talwar

Definitely that was a great tip. Thanks for sharing. It was really helpful.

But I still can't understand why is only({1}....) working in terms of sorting a calculated dimension...

Is there any explation that also will help to understand how Qlik "thinks"?

Thank you in advanced