How to filter on data for the last 5 years from when you run the WF

[CidneyB]

Hello - I am trying to only filter data from the last five years from whenever I run my WF. For example: If I run it today, I want the last 5 years, if I run next week, I only want the last five years. 

I am currently using a filter but I dont want to have to change it every time I run the WF. 

My dates are YYYYMMDD so I need help with trying to create a formula to go along with this date format.

In database and out of database examples would be greatly appreciated.

Thank you

[DataNath]

Hey @CidneyB, here's an example workflow where you can see only the last 5 years of data being taken (I generated all dates from 1st Jan 2015 until today). The real point of focus is the Filter where we're doing the following:

ToDate(DateTimeParse([Actual Date Format],'%Y%m%d'))
>= ToDate(DateTimeAdd(DateTimeToday(),-5,'year'))

You'll just need to replace [Actual Date Format] with your field.

In terms of In-DB, you'll need to use the correct 'flavour' of SQL for whatever warehouse you're connecting to. DateTimeToday() would become Now() or Today() etc. and for parsing you'd likely go down the route of To_Date(<date>, 'yyyymmdd') for the conversion in the Filter.

[CidneyB]

Hey, I'm getting real close. However, I am getting an error for my field. My date field is coming in with a FixedDecimal. The error states "The Formula :Polm_create_date" resulted in a string but the field in numerix. Use ToNumber(....) is this is correct"

[DataNath]

@CidneyB ah I had assumed the date field would be in YYYYMMDD format. If it's a FixedDecimal then just use this in the Filter expression:

ToDate(DateTimeParse(ToString([Actual Date Format]),'%Y%m%d'))
>= ToDate(DateTimeAdd(DateTimeToday(),-5,'year'))

Where we turn the numeric date form into a string so we can use date functions with it.

[CidneyB]

Thank you! That worked, Do you happen to know if there is a way to preform this within the database?

[DataNath]

Great to hear @CidneyB! Do you mean using the In-DB palette in Alteryx or just in general when writing a SQL query? If you tell us what warehouse you're using (i.e. Snowflake, Oracle, MySQL etc) then we can come up with something for you - it's absolutely possible!

[CidneyB]

:) Using the In-DB Plaette within Alteryx!

[CidneyB]

MY SQL

[DataNath]

Hey @CidneyB thanks for that - you should be able to use something like the following within your In-DB Filter tool:

CAST(CAST(CAST("Actual Date Format" AS INT) AS CHAR) AS DATE) >= CAST(DATEADD(YEAR,-5,GETDATE()) AS DATE)

Have attached the amended workflow with the In-DB solution - I've obviously removed my DB connection so you'll need to reconfigure that but you can see the Filter condition etc.

[CidneyB]

@DataNath Thank you!! One additional question. Now lets say, I want to filter the past 5 years only from the beginning of the year. For example:

On February 20th 2024, I run my workflow, but I only want it to pull 01/01/2024 back to 01/01/2019. I want to be able to run the WF on any day after the new year without manually changing the filter. Would this be possible? Out of "in-database" tools are fine.