Filter between dates and dates to be dynamic

[alteryxlim]

Hi, I want to set a filter between two dates and the two dates to be dynamic.

So, essentially, 

Date 1 < data <  Date 2

Date 1 = start of the current month

Data = Target Completion Date (name of the column in my Excel where the data is located)

Date 2 = last day of the current month

I have the following filter formula but it doesn't recognize the "-1". 

I want to the filter to only show me the data between 01-March-2022 and 31-March-2022 (inclusive of the two end dates) and so I set the lower date constraint to March minus 1 for February. Obviously, Alteryx doesn't like my "-1" simple instructions. How would you modify?

ToDate([Target Completion Date]) <= ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth")) AND ToDate([Target Completion Date]) > ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth"-1))

[atcodedog05]

Hi @alteryxlim 

Your formula is corrected. Greater than last of this month and lesser than last of the previous month.

ToDate([Target Completion Date]) <= ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth")) AND

ToDate([Target Completion Date]) > ToDate(DateTimeAdd(DateTimeTrim(DateTimeToday(),"lastofmonth"),-1,"month"))

Try this dates between firstofmonth and lastofmonth

ToDate([Target Completion Date]) >= ToDate(DateTimeTrim(DateTimeToday(),"firstofmonth")) AND
ToDate([Target Completion Date]) <= ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth"))

Hope this helps : )

[RolandSchubert]

Hi @alteryxlim ,

you could try:

ToDate([Target Completion Date]) <= ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth")) AND ToDate([Target Completion Date]) >= ToDate(DateTimeTrim(DateTimeToday(),"firstofmonth"))

Best,

Roland

[alteryxlim]

Thanks @RolandSchubert 

Elegant!

@atcodedog05 I tried the mofidied filter formula you provided. It is error free now but it doesn't show me the correct filtered data. The correct filtered data should have four rows of data and I am only getting one row of data which has Target Completion Date at 31-March-2022. The three other missing rows have dates 30-March-2022 and 01-March-2022. 

Further question: If I want to set three filters in parallel in my workflow:

Filter 1 = Filter above (which works now)

Filter 2 = Same as Filter 1 but this time for previous month, i.e. in this case, February 2022 (as we are in March 2022 now)

Filter 3 = Filter 1 + Filter 2

Is there a keyword that points to the previous month?  

And for the combined filters 1 + 2, perhaps I should use Union tool?

[atcodedog05]

Hi @alteryxlim 

I have updated my post please the corrected formula

ToDate([Target Completion Date]) <= ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth")) AND

ToDate([Target Completion Date]) > ToDate(DateTimeAdd(DateTimeTrim(DateTimeToday(),"lastofmonth"),-1,"month"))

ToDate([Target Completion Date]) > ToDate(DateTimeAdd(DateTimeTrim(DateTimeToday(),"lastofmonth"),-1,"month")) this gives last day of previous month.

Hope this helps : )

[MarqueeCrew]

@RolandH ,

I was just about to post my expression (with time comparison to @atcodedog05 ) and now I have to run with your comparison.  Ok.  I'm done. 

Each expression was evaluated using 1,000,000 (identical) input records running through a filter three (3) times.  The middle times are below:

@atcodedog05 = 470ms

@RolandSchubert  = 465ms

@MarqueeCrew  = 269ms

Want to know why mine is twice (2x) as fast?  It wasn't because of AMP.  It was because I kept it simple:

left([Target Completion Date],6) == left(DateTimeStart(),6)

2022-03 is constant for all days of the month of March in 2022.  

You guys crack me up 🤣🤣🤣🤣🤣🤣

[binuacs]

@alteryxlim another option you can define Date1 and Date2 variable then apply the filter to avoid the complexity 

[RolandSchubert]

Now is the time for a bit of date calculation ...

Same filter for last month:

ToDate([Target Completion Date]) <= DateTimeAdd(DateTimeTrim(DateTimeToday(),"lastofmonth"), -1, 'month') AND ToDate([Target Completion Date]) >= DateTimeAdd(DateTimeTrim(DateTimeToday(),"firstofmonth"), -1, 'month')

both:
ToDate([Target Completion Date]) <=DateTimeTrim(DateTimeToday(),"lastofmonth") AND ToDate([Target Completion Date]) >= DateTimeAdd(DateTimeTrim(DateTimeToday(),"firstofmonth"), -1, 'month')

[atcodedog05]

Hi @MarqueeCrew 

Yup, I agree your method is definitely the best performing 😅😂 I guess we should be doing that.

It takes a little more time to explain but definitely a performance saver 😅😀

@MarqueeCrew  Question which of these are faster DateTimeStart(), DateTimeToday(), DateTimeNow()

Thank you for the knowledge share 🙂

[RolandSchubert]

@MarqueeCrew 

You are so right - if you think about the problem carefully, it will result in a better solution ...