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

Formula for filtering last 3 month

BosKev
8 - 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

 

11 REPLIES 11
JosephSerpis
17 - Castor
17 - Castor

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
16 - Nebula
16 - Nebula

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.

 

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

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

Thableaus
17 - Castor
17 - Castor

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,

jrgo
14 - Magnetar

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

rohini
8 - Asteroid

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
14 - Magnetar

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

Labels