Displaying top 5 in table with repeating values

[natalieghx]

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:

 

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.

 

[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]

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]

you can try rank function with certain offset in set analysis

[natalieghx]

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