In my use case, I have a table of data in which I am counting the number of student IDs per school in selected grades to see the participation per school in a given week.
The filters applied on the table are of “week” and “grade”.
I need to be able to calculate the percentage of active students in a school out of the total students registered in the school since the beginning of the academic year.
For this I need to be able to create a calculated field that avoids the date filter but abides by the grade filter, to count the total number of students in the school for the selected grades.
Is there a way to do this or some other solution to give me the required percentage calculation?
Hi! Level-aware calculations should help you achieve this. I’d recommend experimenting with something like this:
{Total Students Registered} = min(distinctCountOver({Student ID},[{Grade}],PRE_FILTER))
Min is used to force returning a single value only as distinctCountOver in combination with PRE_FILTER returns the same value for every row otherwise. You could also use Max or Avg instead, it should make no difference.
More information on level-aware calculations is available here:
The formula you have suggested will give me the minimum number of students in the entire grade, and will display this across all schools. Similarly, when I run
min(distinctCountOver({Student ID},[{school},{Grade}],PRE_FILTER)), it gives me the number of students in the grade with the lowest number of students for that school.
Essentially I need to get the sum of the distinct count of students across the chosen grades(as per the applied grade filter), across all time(avoiding the applied date filter) for each school. So prefilter wont work as it will avoid both, the grade filter, and the date filter.
I played around with this and haven’t found a way to crack the problem in a dynamic way. The only workaround I could think of without transforming the source data is by using single value filters for class and week. You can parameterise them and reference in calculated fields to code out the expressions. But this would mean the user can only look at one week and one class at a time which doesn’t address your original question.
Other option would be to transform the data and flatten it out during ingestion. Pre-compute columns in SQL for schools and classes as well as the values for the full academic year. This would largely simplify the complexity of the calculation problem in QuickSight but it’d mean you introduce some nasty code to maintain both in SQL and in the calculated fields.
Let me know if you manage to crack this, I would be super-interested to see a proper solution for the problem.
Thank you Istvan! I’ll be sure to post the solution here when/if I find one.
As it stands, if the need for this computation becomes urgent I plan to solve for it at the dataset level itself.
This has been my first query within the QS community and I just want to share that I’m thankful for the timely and friendly engagement with my problem. It encourages me to have more problems and engage with this platform.
+1 to Ying’s solution. @CGauthor, FYI of the Quicksight order of evaluation. You would be able to add the PRE_AGG (rather than PRE_FILTER) to the function so that the grade filter (Analysis Filter) is applied to the calculation but the week filter (top and bottom filter) is not applied.
