Count Distinct over Time period

[DanS_AMS]

Hi all, 

I would like to create a "count distinct" over a 30 days window.

I.e.:

- We have the product ids per day, say 200-300

- We have this data for the last 12 month

- I would like to know the total number of different products (count distinct) for every 30 day period from today back to 12 months ago.

Could you please support? Thanks

[afv2688]

Hello @DanS_AMS ,

do you want it on 30 day periods or based on every month? Also could you please create a mock data to show how it comes?

Regards

[OllieClarke]

Hey @DanS_AMS 

Here's a way of doing it for rolling 30 day groups based on today's date:

These date ranges will update every day, so this might not be the solution you're after...

[DanS_AMS]

Hi @afv2688,

Yes, it would be a 30 days period, i.e. today would be the range 29-10-2021 to 29-11-2021, yesterday would be the range 28-10-2021 to 28-1-2021 and so on.

Let me work on a simplified mock and I will share it here.

Thanks

[DanS_AMS]

Hi @OllieClarke,

thanks for your response. Indeed, I would need it to be static, so for every date, it should show the distinct products of the respective last 30 days.

[OllieClarke]

Hey @DanS_AMS 

Is this more like it?

My dataset didn't include every single day in the year, so the date range will be the min/max of the available dates

[DanS_AMS]

Thanks @OllieClarke,

I think this approach could work, however, I applied it on my sample data and it is really slow. So in practice, this approach doesn't work for me unfortunately.

Is there no "window" function like in Tableau where you can say "count distinct over window (date - 30day)" or a loop/macro or something similar?

Thanks

[OllieClarke]

@DanS_AMS 

There is no window function in native alteryx, so we created our own with the generate rows and join combo. Adding a summarize tool to group by Date before the generate rows should speed up the workflow though. 

You could turn this into a batch macro, but it seemed to run much slower.

You could try and create the batches before the macro, so you can group by something in the macro input, but then you're back to the generate rows approach, so you might as well do as we were...