Formula for filtering last 3 month

[BosKev]

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

[JosephSerpis]

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?

[PhilipMannering]

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.

P 

[BosKev]

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 @JosephSerpis workflow it filtered out 6/3/2019. 6/4/2019...why is that? Thanks.

[JosephSerpis]

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")

[Thableaus]

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.

Cheers,

[jrgo · Accepted solution]

@BosKev 

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

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

[BosKev]

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

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

[rohini]

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")

[jrgo]

Hi @rohini 

I was confused with your question as I thought you were referring to Jan 08, 2019 🙂

Here's an updated expression that should work.

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

Enclosing both sides of the expression in a TODATE() function corrects comparisons expressions when one field is formatted as "Date" and the other as "DateTime". 

@BosKev you may want to adjust your expression as well to this.

Best,

Jimmy
Teknion Data Solutions