Filter Data Based on Current Date

[thenning]

Hello, I'm trying to figure out the best way to accomplish the following: (1) User selects the current date. (2) Based on the selected date, bring in a maximum of the last 36 months worth of data. For example, if I selected 6/8/2018, pull in everything from January 2017-May 2018. At the end of 2019, I'll want to pull in everything from January 2017-December 2019. Then, beginning in 2020, I won't want any 2017 data to pull in. I'm thinking one of the Dynamic tools could accomplish this, but I've never used them before and I've yet to figure it out on my own. Any assistance would be greatly appreciated. Thanks!

[JosephSerpis]

Hi have you tried the date time add formula you can specify the unit of measurements e.g.  + or - 36 months then you could that in a filter?

[CharlieS · Accepted solution]

I think the attached workflow achieves the desired result. Check it out at let me know if that works for you.

The first step is to convert the input data to the standard date format (ISO 8601) that Alteryx recognizes. I converted "January 2017" to "2017-01-01" using this expression: DateTimeParse([Month],"%B %Y"). The first day of the month is automatically populated. 

Then I applied a filter to the data by comparing the newly converted date field and the user input field with the following expression: DateTimeDiff([Input_Date],[FirstofMonth],"months")<=36.

Finally I gave the user a Date tool to input a date (and ensure it would be in the expected format). 

[AlteryxUserFL · Accepted solution]

Hello, 

I have attached a workflow that filters the data based on 36 months from the current date and 36 months based on a user input date. You see both branches. Let me know if this helps :)