Need to Find the missing Months between 2 dates

[Kapil_Lambhate]

Hi All,

i have two dates in my workflow, where i have to find the missing months to be populate in my output column.

[TheMattLeonard · Accepted solution]

The key tool you need for this exercise is the Generate Rows Tool. It is useful when trying to generate possible dates between two specific dates. Follow the steps below and you should be in good shape:

1. If you haven't already, make sure your dates are in the Alteryx date format (yyyy-MM-dd) by using the DateTime Tool.

2. Attach a Generate Rows Tool. Select "Create New Field" and name it "Missing Months". Make sure it is also formatted as a Date. Your Initialization Expression should be [DateTime_Out1]. Your Condition Expression should be [Missing Months] < [New Price Date1]. Your Loop Expression should be DateTimeAdd([Missing Months],1,"Months").

3. Now that we have all the possible months generated, we can filter out the ones we already have with a Filter Tool. Your expression should be the following: DateTimeMonth([Missing Months]) != DateTimeMonth([DateTime_Out1])
AND
DateTimeMonth([Missing Months]) != DateTimeMonth([New Price Date1])

4. If you want the exact format you have in the screenshot, you can use another DateTime tool to convert the Missing Months into the (Jan 23) format. In the DateTime Tool, select "Date/Time format to string, select Missing Months as your field to convert, and put Missing Months2 as the new column name. Select Custom for your new format, and use the following for the custom format: Mon yy.

5. Finally, use a Summarize Tool. Group on your original 2 date fields. Select Missing Months2, and go to Add-->String-->Concatenate. In the bottom of the configuration, type ", " as the separator.

Hope this helps!

[Prometheus]

@Kapil_Lambhate Here's another solution. I used a Text Input tool with the month abbreviations and month number, and then to make the year dynamic (you can make it more dynamic than this), I added a Formula tool to pull in today's year (just the last two digits). Then I brought that together with the dataset using an Append Fields tool. After that, I added a Formula tool where I added the number of months between DateTime_Out1 and New Price Date1 to produce a new month and year for the output. Then I sorted the data on DateTime_Out1 ascending and Num descending, then used a Summarize tool to concatenate the new MoYr while grouping by both date fields.

 

 

 

 

[SPetrie]

If you have the R tools installed, another method would be the Time Series filler tool set to increments of months. You just need to sort the data back after it has done its thing.