Dynamic Date Filter

[sgazar]

Hi,

Hopefully I explain what I'm trying to accomplish in a digestible way...but essentially I have a file with a field containing dates. When I load the file to Alteryx, the date appears in the format of yyyy-mm-dd. I plan to run this Workflow every single Monday (so once a week). That field containing dates will have dates in the past and also dates in the future. Essentially what I want is to filter the dataset so that I only show data with dates dating 12 months in the past and also 4 months into the future. So for example, if today is 10/16/2023, I would like the dataset to be filtered to show data that falls in the range of 11/1/2022 - 2/29/2024. How can I accomplish this? Happy to elaborate further if this did not make sense. Thanks!

[SPetrie · Accepted solution]

You can use a filter tool and a formula similar to this one.

[Date] >= DateTimeAdd(DateTimeToday(),-12,"months") and [Date] <= DateTimeAdd(DateTimeToday(),4,"months")

Only issue I see would be that 4 months from 10/16/23 would be 2/16/24 so 2/29/24 would fall outside the filter.

Edit:

This formula should get you the date range you mentioned in your post.

[Date] >= datetimeadd(DateTimeFirstOfMonth(),-11,"months") and [Date] <= datetimeadd(DateTimeLastOfMonth(),4,"Months")

[Qiu]

@sgazar 
Add to idea of @SPetrie ,we can then use the DateTimeTrim(YourDate, "LastofMonth"). 😁

[sgazar]

Thank to both of you! These worked like a charm