Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Get last day of a random month in a year

Highlighted
8 - Asteroid

I have 1 field which specify a datetime value: MM-YYYY

eg: 01/2020 or 02-2020

 

How to I get the last day of each month in that column?

eg: 31 or 29

Highlighted
15 - Aurora

The way I normally get last day of month is to go the 1st day of the next month and subtract 1 day. So the steps would be:

 

1. add 01 as the day to the start

2. convert to Alteryx date format (yyyy-mm-dd)

3. DateTimeAdd 1 month

4. DateTimeAdd -1 day

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @hungdm I mocked up a workflow let me know what you think? 

 

Last_Day_Month_23062020.JPG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@hungdm ,

 

The solutions provided by both @JosephSerpis  and @DavidP  should be well accepted.  Additionally, I've provided a KB article that's available on community to help you solve the problem too:  https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Marquee-Crew-s-Guide-to-Dates/ta-p/... 

 

I wrote that article to help you understand how to use date functions to better solve use cases.

 

Cheers,


Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels