Top 20 sorting issue ?

[bob654321]

I'm having an issue with displaying the top 20 names in the list. The  names should be sorted based on the total requests count and if the request count is same, sort the names using the last name in ascending order. The issue is the last 2 names are wrong in the list in the attached workbook. How do I sort the names using their last name when the counts are same ?

[Taoufiq_Zarra]

Is this what you're looking for?

Attached file

[sunny_talwar]

Is the chart on the right what you are looking to get?

But it is not just the last two which changed... there were few others which changed as well and that's just because based on your current selections, there are few of the names where total request were the same and the sort order was not ascending by last name.... for example

Last name Kim and Bowman both have total request of 19, but they are currently sorted in descending order of there last names (assuming Kim and Bowman are the last names here)

[bob654321]

Thanks Sunny! The list I have originally was for Top 22. If you change it to Top 20 under the 'Dimension Limits", the list changes again. How do we keep the names sorting consistent when changing the top N value ?

[sunny_talwar · Accepted solution]

Use set analysis in all your expressions instead of using dimensions limits

{<Ordering_Physician_name = {"=Rank(Max(Aggr(Count(DISTINCT Final_Authorization_Key), Ordering_Physician_name, Ordering_Physician_Specialty_ID)) + Rank(Ordering_Physician_Last_Name)/1E10) <= 20"}>}

 

[bob654321]

Thanks again Sunny! I'm also trying to display the unique rank of each physician 1,2,3,4...16 in the same order. How can I do that ?

[sunny_talwar · Accepted solution]

May be add this expression

=If(Column(1) > 0, Rank(Max(Aggr(Count(DISTINCT Final_Authorization_Key), Ordering_Physician_name, Ordering_Physician_Specialty_ID)) + Rank(Ordering_Physician_Last_Name)/1E10))

[bob654321]

That worked!!

[bob654321]

Sunny, another question.

When I include a second dimension, it is showing up 22 physicians instead of only 20 physicians. How can I limit the list to 20 irrespective of how many dimensions I add into the table ?

[sunny_talwar]

Which dimension have you added?