Trailing 12 month rolling distinct count

Hello!

I’m trying to calculate the distinct count for trailing 12 months. For each month in the visual, I want to show a distinct count of the last 12 months

For example,
Jan 2023
distinct count from Feb 2022 to Jan 2023

*Dec 2022
distinct count from Jan 2022 to Dec 2022

Nov 2022
distinct count from Dec 2021 to Nov 2022

Can you do a window sum that sums the distinct counts?

windowSum
(
distinct_count({id})
, [{date} ASC]
, 0
, 12
)

Something like that?

1 Like

Hello and thanks for the response.
Unfortunately, windowSum didn’t quite work because I need a rolling distinct. Figured a workaround for now.
My question now is around filtering. I want to display the current month and last six months. So I create a filter for them. The filter also ends up filtering the data as such.
I need to be able to display only the current and last six months but the calculation should be taking the last 12 months into account. Is there any way to do that?
Thanks a lot

You can look into a LAC-W calculation with PRE_FILTER.

2 Likes

Hi @Madura_Puri ,

I am trying to implement similar thing, windowsum is not working for me. Windowsum is adding the last 12 month distinct counts (sum of each month distinct count), but I want to calculate the distinct count for the last 12 months data for each month in the dataset. Could you please shed somelight on how did you achieve this? Your help is appreciated.

Hi Sindhu! I used a formula to only count IDs the first time they occurred in the time period and then do a running sum across these counts that way ensuring the ID is counted only once for the time peirod. Unfortunately this only works for YTD/same year’s data. Here’s the formula

runningSum(

distinct_count(

ifelse(

{Time Period}=minOver({Time Period}, [advertiserid], PRE_AGG),

advertiserid,

NULL

)),

[{Time Period} ASC],

)