I’m trying to calculate and display the number of distinct users who signed up on a given day via a specific media source, and I want this cohort size to be repeated on every row in my Amazon Quick Sight visual — even when user_id is included. Quick Sight’s distinctCountOver() does return the correct count only if user_id is not present in the visual, but when I add user_id, the function returns 1 for every row because it evaluates per user. I’ve explored using sumOver(1) and custom flags, but they overcount if users have multiple rows. I need a way to consistently display the correct distinct user count per (signup_time + media_source) on every row, regardless of the level of detail in the visual. Is this even possible?
You will need to use the Level-Aware Aggregation (LAA) by calculating the distinct user count at the cohort level ( Signup_time and media_source) and have the value repeated for every user row in the visual. You can review documentation here
