topic Re: How to get the first N - using a string function - dimension value in App Development

How to get the first N - using a string function - dimension value

datagrrl — Fri, 03 Mar 2023 15:22:09 GMT

This seems simple, but it is evading me this morning.

If I want to see the "Top 2" or "bottom 2" (or whatever number) alphabetically (nothing to do with the measures) how would I do that? In my example I only want to see Mexico and the USA, but if Turkey was added I would want to see Turkey and the US. I want to see the last two alphabetically.

This is a dummy example, and I included my dummy file.

Any assistance is appreciated.

Re: How to get the first N - using a string function - dimension value

Oleg_Troyansky — Fri, 03 Mar 2023 17:04:14 GMT

Hi Datagrrl,

This is a tricky one... Since Dimension Limits can only be applied to the first Measure, which is Sales, there is no easy way of limiting the chart to the top (bottom) entries in the alphabetical order.

The only way is to limit the data in the Measure using Set Analysis. I managed to get the last two countries (Mexico and US) with the following formula. For other situations, you may need to tweak it a bit, but you can see the general direction:

Sum({<Country={"=rank(Country)<=2"}>} Sales)

To my great surprise, the function Rank() appears to work for strings as well!

Cheers,

Re: How to get the first N - using a string function - dimension value

datagrrl — Wed, 22 Mar 2023 13:44:35 GMT

Thanks, this worked. Weird that I had to add an extra one, but I am guessing that Null or blank must be ranked at the end in my real data set (needs cleaning).