KPI objects: returning a rank for a specific dimension value

[#1]

Hi there

I am having some trouble returning a rank in a KPI object.  I have used the expression from this previous thread: Rank in Textbox as KPI  but this approach does not seem to work with my data.

I have survey data with respondents from around 20 countries and they were asked which destinations they were considering visiting from a list of 50-odd destinations. I want to return Australia's rank, i.e. where Australia is placed among the 50-odd destinations. I need to be able to filter the data to obtain ranks by country of origin, for example.

See attached files. I have been able to show the rank of each destination in a bar chart. I have also been able to return the name of the destination which is ranked #1 (first KPI on the left), and I can filter this by origin.

However, using the expression: aggr(rank(TOTAL Sum({$<Destination={"Australia"}>} Selection)), Destination) just returns '1' even when Australia is not ranked #1.

What method do I need to use to show Australia's rank among various sub-groups of the data?

Any help would be greatly appreciated.

Thank you

Emma

[sunny_talwar · Accepted solution]

Try this:

Only({$<Destination={"Australia"}>}Aggr(Rank(Sum(Selection)), Destination))

[#3]

Hi Sunny

Perfect, thank you so much!

Emma

[pleiker44]

Thanks so much for this answer!  I've been trying to figure out how to subset the result of an aggr for use in a KPI all morning.  This did the trick!

Pat

[Vimarsh]

Hi Sunny,

I was trying another expression to change the destination based on field selection.

var1=GetFieldSelections(Destination)

Only({1<Destination={"$(var1)"}>}Aggr(Rank(Sum(Selection)),Destination))

Couldn't make it work. Can you tell what could be wrong. Appreciate your help!

Thanks!
VS

[murozel76]

Thanks! This worked for me, too.

[rohansoni649]

Hi Vimarsh, 

Did you get the solution for above issue, when you are selecting a Destination and getting the corresonding Rank in the KPI or any other object.

Please let me know if solved. Would be a great help.

Rohan