topic Re: Top 20 sorting issue ? in QlikView

Top 20 sorting issue ?

bob654321 — Sat, 16 Nov 2024 19:22:35 GMT

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 ?

Re: Top 20 sorting issue ?

Taoufiq_Zarra — Mon, 30 Dec 2019 08:46:18 GMT

Is this what you're looking for?

Attached file

Re: Top 20 sorting issue ?

sunny_talwar — Mon, 30 Dec 2019 11:29:07 GMT

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)

Re: Top 20 sorting issue ?

bob654321 — Mon, 30 Dec 2019 14:10:32 GMT

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 ?

Re: Top 20 sorting issue ?

sunny_talwar — Mon, 30 Dec 2019 14:22:21 GMT

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"}>}

Re: Top 20 sorting issue ?

bob654321 — Mon, 30 Dec 2019 14:28:37 GMT

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 ?

Re: Top 20 sorting issue ?

sunny_talwar — Mon, 30 Dec 2019 14:33:59 GMT

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))

Re: Top 20 sorting issue ?

bob654321 — Mon, 30 Dec 2019 14:47:29 GMT

That worked!!

Re: Top 20 sorting issue ?

bob654321 — Mon, 30 Dec 2019 18:24:58 GMT

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 ?

Re: Top 20 sorting issue ?

sunny_talwar — Mon, 30 Dec 2019 18:56:07 GMT

Which dimension have you added?

Re: Top 20 sorting issue ?

bob654321 — Mon, 30 Dec 2019 19:00:30 GMT

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.

Re: Top 20 sorting issue ?

sunny_talwar — Mon, 30 Dec 2019 19:38:39 GMT

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?

Re: Top 20 sorting issue ?

bob654321 — Mon, 30 Dec 2019 19:44:07 GMT

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

Re: Top 20 sorting issue ?

sunny_talwar — Tue, 31 Dec 2019 13:10:38 GMT

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.

Re: Top 20 sorting issue ?

bob654321 — Tue, 31 Dec 2019 13:30:56 GMT

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 ?

Re: Top 20 sorting issue ?

sunny_talwar — Tue, 31 Dec 2019 14:05:04 GMT

What is your expression for prior period?

Re: Top 20 sorting issue ?

bob654321 — Tue, 31 Dec 2019 14:10:21 GMT

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))

Re: Top 20 sorting issue ?

sunny_talwar — Tue, 31 Dec 2019 14:26:03 GMT

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

Re: Top 20 sorting issue ?

sunny_talwar — Tue, 31 Dec 2019 14:27:00 GMT

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

Re: Top 20 sorting issue ?

bob654321 — Tue, 31 Dec 2019 14:41:10 GMT

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.