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))
Solved! Go to Solution.
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 : )
Hi @alteryxlim ,
you could try:
ToDate([Target Completion Date]) <= ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth")) AND ToDate([Target Completion Date]) >= ToDate(DateTimeTrim(DateTimeToday(),"firstofmonth"))
Best,
Roland
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?
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 : )
@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 🤣🤣🤣🤣🤣🤣
@alteryxlim another option you can define Date1 and Date2 variable then apply the filter to avoid the complexity
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')
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 🙂
@MarqueeCrew
You are so right - if you think about the problem carefully, it will result in a better solution ...