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

Formula for filtering last 3 month

Asteroid

Hi All,

 

Question on date filtering. I have a list of dates in a field I applied the formula below to filter out the latest 3 months of date but its not working. Could someone help? 

 

One item of note on the date field is that it will always end at last day of the prior month. Example in September I  will get a data file from Jan 01 2018 till august 31 2019.

 

my formula:

[Completion Date] >= DateTimeAdd(DateTimeToday(), -3, "months")

 

sample data set below: I want it to capture latest 3 months.

 

Completion Date
5/22/2019
5/31/2019
6/3/2019
6/4/2019
6/5/2019
6/6/2019
6/29/2019
6/30/2019
7/14/2019
7/29/2019
7/30/2019
7/31/2019
8/1/2019
8/2/2019
8/3/2019
8/29/2019
8/30/2019

 

Alteryx Certified Partner

Hi @BosKev the issue is your date field is not a date type I have mocked up a workflow that should work. Let me know what you think?

Alteryx Certified Partner

You need to format your field as a date data type first.

 

Use this formula,

 

 

datetimeparse([Completion Date],'%m/%d/%Y') >= DateTimeAdd(DateTimeToday(), -3, "months")

 

 

Hope this helps.

 

Asteroid
Spoiler
Thanks guys but the field is already formatted as "Date" the problem is this formula does not return all the dates within the last 3 month. I ran @JS420 workflow it filtered out 6/3/2019. 6/4/2019...why is that? Thanks.
Alteryx Certified Partner

Hi @BosKev you field is not the corret date format for Alteryx as it likes dates in YYYY-MM-DD so hence why we need to change the date format before we apply the formula those dates are being filtered out because the are over 3 months ago. I produced the below using a datetimediff formula with this syntax DateTimeDiff(DateTimeParse([Completion Date],"%m/%d/%Y"),DateTimeToday(),"Months")

 

Dates.PNG

Alteryx Certified Partner
Alteryx Certified Partner

Hi @BosKev 

 

I'd use @PhilipMannering solution, but I'd add that you need to put a ToDate() in front of your condition so it doesn't convert to a DateTime (then, you have problems comparing a DateTime with a Date).

 

datetimeparse([Completion Date],'%m/%d/%Y') >= ToDate(DateTimeAdd(DateTimeToday(), -3, "months"))

 

This should work perfectly.

 

clipboard_image_0.png

 

Cheers,

Alteryx Certified Partner

@BosKev 

 

it sounds like you want to filter the dates for the last 3 full months. If so, using the solution @JS420 provided, you can update the filters expression to the below:

[DateTime_Out] >= DateTimeAdd(DateTimeFirstOfMonth(), -3, "months")

 

Highlighted
Asteroid

Thanks so much...sorry wasn't able to explain clearer the first time.

 

I didn't understand DateTimeToday(), -3, "months") vs DateTimeFirstOfMonth(), -3, "months")

Meteoroid

Hi I tried using the same formula to get one month data but this is excluding the 01/08/2019 data, if I am taking data for the month of August. Can you please help

 

[CNF Received Date 1] >= DateTimeAdd(DateTimeFirstOfMonth(),-1,"months")

Labels