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 ?

[bob654321]

I have added Ordering_Physician_client_phys_id. There could be multiple Ordering_Physician_client_phys_id values for each physician but the table should only display the lowest value if there are multiple Ordering_Physician_client_phys_id values for a specific physician.

[sunny_talwar]

When you add Ordering_Physician_client_phys_id... you get two rows for HOCKENBERRY, LESTER M with two different Ordering_Physician_client_phys_id. What would you want to do here?

[bob654321]

It should pick up   "P100001499830006" since it has 30 which is the highest total request count for that person's related client phys id. 

[sunny_talwar]

I think the way everything is structured, we would need to know the dimensions in advance to right the expression here... it would be difficult to come up with an expression which will work in all scenarios.

[bob654321]

Thanks Sunny!  One last question, I know you created the expression for the Current Rank in the table. How to create the expression for the Prior Period Rank ? 

[sunny_talwar]

What is your expression for prior period?

[bob654321]

This is my expression for Prior Total Requests.

Max({<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"}>}Aggr(
Count({<fnl_determ_year={$(=Max(fnl_determ_year))},Quarter={"$(='Q' & (purgechar(GetFieldSelections(Quarter),'Q')-1))"}>}DISTINCT Final_Authorization_Key)
, Ordering_Physician_name, Ordering_Physician_Specialty_ID))

[sunny_talwar]

Do you need the Previous Period Rank for the Physicians which are Ranked top 20 in the recent period?

[sunny_talwar]

Also, the sample app that you provided, only included data for Q3... would you be able to add Q2 and share?

[bob654321]

 Yes, i need a Previous period Rank column based on the previous request count.

I've added more quarters and years in the attached workbook.