Filter a Date field to return all items <= first day of the current month

[alphabex18]

Hi everyone, I am using the filter tool, where each month, I manually alter the date to return the results I need (shown below). I would like to add this workflow to a scheduler service, but cannot because of my manual changes. 

How do i convert the calculation below (still using my filter tool) to return 

Current State

[MemberEnrollmentDate] <= "2019-05-01"

Future State Requirement

[MemberEnrollmentDate] <= first day of the current month (based on the day the workflow us run)

*so, if i ran the workflow on 05/31, it would return member enrollment for May (and earlier), but if i ran the workflow on 06/01, it would now return member enrollment for June (and earlier).

Thank you ahead of time

[Kenda]

Hey @alphabex18 

Lucky for you, this is easy in Alteryx! Simply change your expression to:

[MemberEnrollmentDate] <= DateTimeFirstOfMonth()

[alphabex18]

Thank you Barnes, that worked perfectly. However, I have an "AND" statement in another filter with the same concept, that I get zero results when i replace the '2019-05-01' with the DateTimeFirstOfMonth(). Any suggestions?

[MemberEnrollmentDate] = [GrpEnrollmentDate]

AND
[MemberEnrollmentDate] = DateTimeFirstOfMonth()

[Thableaus]

Hi @alphabex18 

Is your [MemberEnrollmentDate] field a Date type or a DateTime type?

Also, could you show us an example (of data) where this doesn't work as you expect?

Cheers,

[Kenda · Accepted solution]

@alphabex18 

Try changing your DateTimeFirstOfMonth() to 

todate(DateTimefirstOfMonth())

If this doesn't work, do you have some sample data you could provide? 

[alphabex18]

Adding the todate to the beginning of the DateTimeFirstOfMonth worked. My data type was Date only, not DateTime....can you tell me why the ToDate portion is necessary, so i understand that better?

[Thableaus]

@alphabex18 

That's because DateTimeFirstofMonth() results in a DateTime, not a date.

Specifically for this month, the result is 2018-05-01 00:00:00

So if you're comparing 2018-05-01 with 2018-05-01 00:00:00, you might not get the results you need.

I recommend you to read this well explained article by @MarqueeCrew that help you to avoid this date frustration.

Be careful when working with both formats - it can be tricky.

Cheers,

[Kenda]

@alphabex18 

Absolutely!

So when you use the DateTimeFirstOfMonth() function, it will give you both the date and time (at midnight) for the first of the month that you're currently in. This is briefly noted in the "Current Date and Time" section of the Alteryx documentation (https://help.alteryx.com/2018.1/index.htm#Reference/DateTimeFunctions.htm). By wrapping this in the todate() function, you are getting rid of the time portion of this string, leaving only the date. This is what you want here, because you were originally filtering on '2019-05-01' which is just a date. Below is an example of what the value would be if you used just the DateTimeFirstOfMonth() function versus wrapping it in the todate() function. 

Hope this helps!

[alphabex18]

got it! Thank you so much. All is working as expected now, and i never have to go in and change my dates!!!! A big win!