Trailing 12 month rolling distinct count

Question & Answer
, ,
1

Capture
Capture1592×130 10.4 KB

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

2

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

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

Something like that?

3

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

4

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