Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
natalieghx
Contributor II
Contributor II

Displaying top 5 in table with repeating values

Hi, I have a table that shows the Top 5 schools and the number of students in that school in 2017 and 2018. I would like to find the top 5 schools with the highest number of students in 2018.

To do so, I used this code as a calculated dimension:

=Aggr(If(Count(DISTINCT If(Year=2018, Student_ID)) >=Max(TOTAL Aggr(Count(DISTINCT If(Year=2018, Student_ID)), School), 5), School), School)

The result is the table below:

 

 Top 5 Schools.PNG

However, I want to get the table below where if 2 schools have the same number of students (e.g. School 8 & 15) , the table is display the next school with the highest number of students (e.g. School 6 & 18) until there is a total of 5 unique number of students.

 

 Top 5 Schools Correct.PNG

Labels (1)
  • top5

5 Replies
sunny_talwar

Your expression seems to be working fine for me.... the only thing I really changed when I tested was to change the inner if statement to set analysis.... but that should not change the output... would you be able to share a sample to show the issue?

=Aggr(If(Count(DISTINCT {<Year = {2018}>} Student_ID) >= Max(TOTAL Aggr(Count(DISTINCT {<Year = {2018}>} Student_ID), School), 5), School), School)
natalieghx
Contributor II
Contributor II
Author

Hi, I'm afraid I am not able to share a sample to show the issue. Sorry! However, I changed the inner if statement to set analysis but like you said, it did not change the output but thank you for your response!

sunny_talwar

no problem. I hope someone else is able to help.

Best,
Sunny
Anonymous
Not applicable

you can try rank function with certain offset in set analysis

natalieghx
Contributor II
Contributor II
Author

Thank you for your reply. I tried using Rank Function

=IF(Aggr(Rank(Count(DISTINCT {<Year= {1820}>} Student_ID),4),School)<=5,School)

But the output only shows the top 4 schools as shown below

ranking.PNG