Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to set the date to a dynamic date anchored to end of month in a filter or formula

alteryxlim
7 - Meteor

Hi,

 

I'd like to create a workflow whereby each month, Alteryx will filter or prepare a list of action owners who need to close out their action items by the end of that month. I have my data source (Excel).I can do a manual filter and set the date, but I want an automatic (dynamic) instruction in Alteryx that will automatically set the criteria to end of the month (or last day of that month).

 

What is the best way to achieve this? 

 

--HK

7 REPLIES 7
JosephSerpis
16 - Nebula
16 - Nebula

The function DatetimeTrim is perfect for this example as you can specify last of the month.

 

Datetimetrim.JPG

atcodedog05
22 - Nova
22 - Nova

Hi @alteryxlim 

 

Use a formula in a filter like this

 

atcodedog05_0-1645210022947.png

 

Hope this helps : )

 

alteryxlim
7 - Meteor

Thanks  @atcodedog05

 

What is wrong with what I have in the screenshot?

 

alteryxlim_0-1645210790502.png

 

NikyN
9 - Comet

Hello @alteryxlim,

 

For sure the first part of the condition --- formula ToDate([Target Completion Date]) --- if the Target Completion Date is not in the date format, than you need to use DateTimeParse(dt,f) function to change you date to date format.

 

Eg. if your date is in format 18 Feb 2021, than with formula DateTimeParse([DATE],'%d %b %Y') you can change it to date format.

 

Best Luck!

Niky

atcodedog05
22 - Nova
22 - Nova

Hi @alteryxlim 

 

You can Todate() for both but its a must for trim function since it gives output in datetime

 

ToDate([Date])=ToDate(DateTimeTrim([Date],"lastofmonth"))

 

As @NikyN if the date is not in Alteryx dateformat you would need to use parse function to convert it to date.

 

Hope this helps : )

alteryxlim
7 - Meteor

Thanks @atcodedog05 @NikyN

It works now.

 

Is it correct to say that I must ToDate the result of DateTimeTrim because after applying the DateTimeTrim function, the output might not be recognized by Alteryx as a data format, hence I will need to ToDate(DateTimeTrim[TargetCompletionDate],"lastofmonth") ? 

 

alteryxlim_0-1645224890317.png

 

atcodedog05
22 - Nova
22 - Nova

Hi @alteryxlim 

 

Just using lastofmonth will give you the last minute of the month like below 2022-02-28 23:59:59 since our date doesn't have time it wont match when we use ToDate it becomes 2022-02-28 (I,e considering only datepart). This way now it will match.

 

atcodedog05_0-1645253831118.png

 

Hope this helps : )

Labels