How to compute distinct count of month

i tried to use distinct_count({Month}). distinct month count is correct by student but the grand total is incorrect… it gives 5 instead of 10.

image

@ctine - This is because at the visual level there are only 5 distinct Months available (across all the students). Since you are using distinct_count as the aggregation, it behaves in that way i.e. computing the distinct count of months across all students and showing the same in the Total. Hope this helps!

thanks @sagmukhe
any suggestion if what is the formula we should use that could give distinct count of month in student level and in Total sum of distinct count?

@ctine - Can you please try out the below formula and let me know if this helps!

sumOver(distinct_count(month), [student], POST_AGG_FILTER)

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

Yes that works if the student dimension is in visual .if i use it in visual like card or gauge it is giving error because the formula is expecting for student dimension.

@ctine That’s how the LAC function works as it needs context. What’s visual you are trying to fit in? Since your snapshot contained tabular structure, I tested my expressions based on that. If you can share your visual and it’s elements details, I can give it another try!!! Thank you!

@sagmukhe here’s my actual data & visuals. im new in quicksight and i been searching other suggested resolution but i really find it hard to get a accurate solution…

this is the formula:
sumOver(distinct_count({Start Date Year Month}), [{Student ID}], POST_AGG_FILTER)

@ctine - You can perhaps then follow the below calculation:

sum(distinct_count({Start Date Year Month}), [{Student ID}]))

I have provided a sample snapshot where I replicated your scenario using my sample dataset. Hope this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

1 Like