Count Distinct Over N Prior Months?

Question & Answer
, ,
1

I’m looking to calculate a retention rate, but we want to do it over multiple prior months. Expressed in human terms, I’d like to say “Count the distinct user ids in the 2 months before to the start date of this month” and then compare that to “Count distinct user ids in last month and this month.”

The data themselves are at the user-transaction level, not the month level. It’s a bit like “user_id purchased X on whatever date.” I am then mostly making visuals that are aggregating these data by month - so like “here’s how many unique users bought something this month.”

So mostly, if I’m making a visual by month, I’m not sure how to say “count distinct across two previous months.” Here is some of what I’ve tried:

  • There are the “windowCount” kinds of functions, but they seem to operate on rows, not ranges, and since the number of rows in any given month are all over, I’m not sure how that’d work. Besides, there doesn’t seem to be a count distinct one anyway.
  • There’s periodToDateCountOverTime, but that only seems to go back in specified periods - like a single month or a quarter. I didn’t see a way to say “N months.”
  • I can obviously create a measure like “Unique Users” that’s just distinct_count({user_id}) and then do lag on that. That works for comparing over one month, but I don’t see how to get two time periods that would be unique across both.
2

Hello @toomanypuppies, I think the best way to handle this would also allow for some dynamic selection. What I did to test this on my end was create 3 calculated fields.

I made a dateMonth field that uses truncDate('MM', {Order Date}) to convert my dates to a month format for grouping.

Then I made a distinctCountOver function to get the total number of IDs per month. distinctCountOver({user_id}, [{dateMonth}], PRE_AGG)

Now before making the function that would grab my comparison date, I created a integer parameter with a default value of 1 and called it comparisonMonth. Then I made a slider control set the min to 1, max to 12, and intervals of 1. This allows the user to select which month of the last 12 they would want to compare to the current month averages. Then for the last calculated field I used this:
lag(distinct_count({user_id}), [{dateMonth} ASC], ${comparisonMonth})

Then depending on the slider value selected, it will update which month I am comparing to in the visual. This will require some visual constraints to maintain the grouping necessary. It would work in a KPI where it is filtered to this month, or in a table that shows dateMonth, the distinctCountOver function, and the lag function. This should give you the solution you are looking for!