Hello.
I’m hoping that someone might be able to help me. I’m trying to count the number of users that have completed all three courses in this table, the result will then be displayed in a KPI visualisation.
As I couldn’t use sumIf to calculate the status because it is a string, I used parseInt to convert the string to an integer. I’ve tried many ways to try and count or sum a result but I cannot figure it out.
Can anyone provide any tips / guidance?
Thank you,
Robert
Hi Robert,
You can use level aware aggregate to achieve this. Below are the steps to use it for any number of courses.
Find course completed by each user
coursecompletedbyuser: sumOver(CompletedInt,[Name],PRE_FILTER)
2.Find total number of courses
total courses: distinctCountOver(Course, [],PRE_FILTER)
Calculate CorseCompletions percent by user
coursecompletionpercent: {coursecompletedbyUser_PRE_FILTER}/{total courses}
Count the users with 100% completion
userwith100percentcompletion: distinct_countIf(Name,coursecompletionpercent =1)
Put it all in one formula:
distinct_countIf(Name,sumOver(CompletedInt,[Name],PRE_FILTER)=distinctCountOver(Course, [],PRE_FILTER) )
We have an upcoming ask the expert session on level aware aggregates. See the invite here: Ask the Expert - Level Aware Aggregates (April 1st at 11:30am-12pm PT). If you aren’t able to join then we will also post the session in this section of the community in the coming week: Ask the Expert - Amazon QuickSight
Hello n_vetri,
Thank you for you thorough advise, I will try your solution shortly I have just joined the exert session 
Thank you so much for replying to my post.
Kind Regards
Robert
Hello n_vetri,
Thank you for your detailed advise, your solution works perfectly. I understand the level aware aggregates now, the expert session was great too.
Many thanks for your time and assistance today.
Kind Regards
Robert
