Missed the Q4 Fall Release Product Update? Watch the on-demand webinar for more info on the latest in Designer 24.2, Auto Insights Magic Reports, and more!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
GELÖST

Need to Find the Months Between Two Dates

ashleyinman
Asteroid

I have the data attached. 

I'm looking to find all the months between the created date and the delv date. 

Ex.

Order Created DateDelv DateLine NumberlocNumberqty
3/30/20186/14/20181.110100001

 

 

This is what I would like it to show. I do not want to show the month of the Delv Date. 

Order Created DateDelv DateLine NumberlocNumberqtyMonths Between
3/30/20186/14/20181.110100001Mar-18
3/30/20186/14/20181.110100001Apr-18
3/30/20186/14/20181.110100001May-18

 

I have tried using a generate rows tool but I keep getting the error "The value did not change after the Loop Expression"

Initialization Expression: [Order Created Date]

Condition Expression: [Order Created Date] < [Delv Date]

Loop Expression: DateTimeAdd([Order Created Date],1,'months')

1 ANTWORT 1
jdunkerley79
ACE Emeritus
ACE Emeritus

I would suggest a generate rows tool creating a new Date field with formula:

 

Init: DateTimeTrim([Order Created Date],"firstofmonth")
Condition: [MonthsBetween] < DateTimeTrim([Delv Date],"firstofmonth")
Loop: DateTimeAdd([MonthsBetween],1,"months")

 

 

This will produce a column of 1st of the months. You can then use a DateTime tool to format as MMM-YY

 

Sample attached

Beschriftungen
Top-Lösungs-Autoren