Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Filtering rolling date range

MCDR929
8 - 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!

4 REPLIES 4
JosephSerpis
17 - Castor
17 - Castor

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.

jrgo
14 - Magnetar

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

MCDR929
8 - Asteroid

Worked perfectly - thank you!

MCDR929
8 - Asteroid

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

Labels