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.
@RolandSchubert my motto:
Ready. FIRE! Aim.
Cheers,
Mark
@MarqueeCrew How does your full filter formula look like?
Sorry, I'm still trying to catch up to speed and digest the proposed solutions from the Alteryx giants @atcodedog05 @RolandSchubert
For now, I have used the solution from @RolandSchubert and it works but wouldn't mind to understand the LEFT function and how it's integrated into your full formula.
Hi @alteryxlim
It would be something like this
Current month:
left([Target Completion Date],6) == left(DateTimeToday(),6)
Previous month:
left([Target Completion Date],6) == left(DateTimeAdd(DateTimeToday(),1,"month"),6)
The idea is year and month will stay the same throughout the month which 2022-03 or 2022-02
Hope this helps : )
here is my full picture and workflow:
Thanks @atcodedog05 @MarqueeCrew
I guess this only works if the Month and Year stay the same for the duration of the 30/31 days. To have something dynamic (i.e when it's April, May, June.....Nov, Dec) and when it switches to Year 2023, the solutions from @atcodedog05 and @RolandSchubert would accommodate for this dynamicity.
Happy to help : ) @alteryxlim
Cheers and have a nice day!