Generate Months Using the generate rows function

[Tim6]

Hi There, 

I am trying to generate rows in alteryx. 

The date must be repeated every month for the next next 12 months 

the date is October 31, 2019

so basically the next couple days should be 

October 31st, 2019 

November 30th 2019

December 31st 2019

January 31st, 2020

I am using the loop: 

DateTimeAdd([Generated Month],1,'Month')

but it is giving me the following dates:

October 31st, 2019 

November 30th 2019

December 30th 2019

January 30th, 2020

can you please help. 

Thank you so much

[AngelosPachis]

Hi @Tim6 ,

Maybe you can use the following workflow? I have used a generate rows tool to generate 12 months for each record you have initially and then you can use a formula tool to make the date calcs.

Does it have to be in a single generate rows tool?

Hope that helps, let me know if that worked for you.

Cheers,

Angelos

[apathetichell]

For generate rows:

new field (newdate) - type date.

initialization expression at your starting date ("2019-10-31") or use a field.

conditional expression:

[newdate]<="2021-10-31"

loop expression:

datetimeadd(datetimeadd(DateTimeadd([newdate],1,"days"),1,"months"),-1,"days")

[Shalz]

Start and End being your date ranges

Initialization Expression

[Start]

Condition Expression

[Date]<=[End]

Looping Expression

DateTimeAdd([Date],1,'months')

[apathetichell]

logically that makes sense - and it works for the first month but

whereas for 10/31/2019

DateTimeAdd([Date],2,'months') is 12/31/2019

for 11/30/2019

DateTimeAdd([Date],1,'months') is 12/30/2019.

So if you generate integers and then datetimeadd them in a second column it'll totally work - but just datetimeadding them will start giving you the wrong dates after the first month.

[Shalz]

once you generated these rows...you can use formula and get the last date of the month or whichever date you want for that month