Cumulative Distinct Count

Q&A
, ,
1

Hello, I’m trying to create a formula in QuickSight so that month by month it calculates the cumulative number of unique users we have. I’ve tried using runningSum and distinctCountOver, but I haven’t been able to achieve my goal.

What I want is to create, for each month, the cumulative count of unique customers we have throughout the year. For example, if a user made purchases in January, February, and March, in the cumulative total for March they should count as one, because it is the same unique user.

I’ll show you an example where what I want to calculate is the column on the right.

Month Unique Users (distinct count) Unique Users (Cumulative)
Jan-26 1000 1000
Feb-26 700 1200
Mar-26 200 1250
Apr-26 250 1300
…
Total 1300
2

Hi @KLIKIN and welcome back to the Community!

So I think I may have created a solution to your instance. With my solution it will require you to create 3 calculated fields. This first calculated field notes each unique purchase:

first_purchase_date = minOver({Order Date}, [{Customer ID}], PRE_AGG)

The next calculated field flags each user as new only in the month they first appeared:

isNewUser = ifelse(truncDate(“MM”, {Order Date}) = truncDate(“MM”, {first_purchase_date}),1,0)

Lastly, this calculated field computes the cumulative total:

CumulativeUniqueUsers = runningSum(sum(isNewUser), [truncDate(“MM”, {Order Date}) ASC])

One thing to keep in mind is this solution is built around monthly aggregates, so it’s designed to work at the month grain. If you switch to a weekly or daily aggregate, the results may look a little off since the ‘isNewUser’ flag is comparing dates at the month level.

Try this out and let me know how it goes. It may not fully work in your environment but I think this can help get you closer to your solution.