community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Filtering rolling date range

Asteroid

I have seen many posts similar to this, but none are working and I'm not sure why.

 

I have a large data set, and I need it to pull only the last 6 months of data every time it runs.  The date field is CREATE_DATE_TIME, in format YYYY-MM-DD hh:mm:ss.

 

So I need a formula in my filter that will only pass along the last 6 months in the create_date_time field.

 

I feel like this should be really simple, yet I've been banging my head on my keyboard all day.  Please help!

Alteryx Certified Partner

Hi @MCDR929 try this syntax in a formula tool datetimediff(DateTimeNow(),DateTimeParse([create_date_time ],"%Y-%m-%d %H:%M:%S"),"Months") it takes your date field parses to ensure its a data and does a datatimediff from Date Time Now function and gives back the results in months. So you can then easily filter anything within the last 6 months.

Alteryx Certified Partner

Hi @MCDR929 

 

Try using this expression in your Filter tool.

 

TODATE([CREATE_DATE_TIME]) >= TODATE(DATETIMEADD(DATETIMEFIRSTOFMONTH(), -6, 'months'))

 

This assumes that you are looking to filter to the last 6 FULL months (>= 2019-03-01), not just 6 months from today (>= 2019-03-10). However, if you were trying to do the latter, change DATETIMEFIRSTOFMONTH to DATETIMETODAY.

 

Best,

Jimmy
Teknion Data Solutions

Asteroid

Worked perfectly - thank you!

Highlighted
Asteroid

@jrgo - This didn't work for me, I think because of the reason @JS420 pointed out.  Something screwy with my date format, maybe.  But I got it, finally.  Thanks for the assistance!

Labels