topic Re: Displaying top 5 in table with repeating values in App Development

Displaying top 5 in table with repeating values

natalieghx — Sat, 16 Nov 2024 21:50:25 GMT

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.

Re: Displaying top 5 in table with repeating values

sunny_talwar — Fri, 23 Nov 2018 12:38:01 GMT

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)

Re: Displaying top 5 in table with repeating values

natalieghx — Sat, 24 Nov 2018 03:11:04 GMT

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!

Re: Displaying top 5 in table with repeating values

sunny_talwar — Mon, 26 Nov 2018 01:08:06 GMT

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

Best,
Sunny

Re: Displaying top 5 in table with repeating values

Anonymous — Mon, 26 Nov 2018 09:08:31 GMT

you can try rank function with certain offset in set analysis

Re: Displaying top 5 in table with repeating values

natalieghx — Wed, 28 Nov 2018 05:05:52 GMT

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