Trailing 12 month distinct counts for each month in the dataset

[sindhu]

Hi,

I am trying to add a visual to show the last 12 month distinct counts for each month in the dataset. Windowsum is not working for me, as it is summing up the distinct counts for the last 12 months. I want to get the distinct count on the last 12 months data, rather than summing the distinct counts for each month in the last 12 months. It is related to the below post and author mentioned that they are able to implement the workaround. Can someone help me on how to achieve it?

Trailing 12 month rolling distinct count

[Capture] 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

[Max]

Here’s a calculation to get your dates grouped to the last twelve months.

truncDate(‘YYYY’,addDateTime(-dateDiff(truncDate(‘YYYY’,{arrival_timestamp}),now()),‘DD’,{arrival_timestamp}))

This would be to get the distinct Count if it’s this year.
avg(distinctCountOver(ifelse({last tweleve months}=addDateTime(-1,‘YYYY’,now()),{session_id},NULL),[{last tweleve months}],PRE_AGG))

Let me know if that works for you.