Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
bob654321
Contributor
Contributor

Top 20 sorting issue ?

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 ?

Labels (3)
2 Solutions

Accepted Solutions
sunny_talwar

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

image.png 

View solution in original post

sunny_talwar

May be add this expressionimage.png

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

 

View solution in original post

27 Replies
Taoufiq_Zarra

Is this what you're looking for?

Attached file

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
sunny_talwar

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

image.png

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

Capture.png

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
Contributor
Contributor
Author

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

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

image.png 

View solution in original post

bob654321
Contributor
Contributor
Author

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

May be add this expressionimage.png

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

 

View solution in original post

bob654321
Contributor
Contributor
Author

That worked!!

bob654321
Contributor
Contributor
Author

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?