Using Generate Rows Tool to add Future Dates to a TS Forecast Factory tool Output

[OneandOnly13]

Hi,

I have an output from the TS Forecast Factory tool that groups by a field called activity. Each activity forecasts out 90 periods (90 days). The output doesn’t add dates so I’m trying to use a Generate Rows tool to add these future dates to each 90 days of forecasts for each group. The eventual goal will be to add them to the actuals file used in the model.

Example Data:

Activity    Period    Sub Period    Forecasts...

A                1               2                 ######

A                1               3                 ######

A                1               4                 ######

A                1               5                 ######

B                1               2                 ######

B                1               3                 ######

B                1               4                 ######

The gist of the formulas are to always take yesterday’s date and generate 90 days forward for each activity and then reset and loop again once it hits the next activity (basically by counting to 90 and then looping). Here is what I have now:

 

The Generate Tool adds 1 day from yesterday which works but it doesn’t stop at 90. It goes until it errors at 9999-12-31. I think I need help with the Condition Expression on resetting the loop every 90 rows.

What should I change in my generate rows tool to be able to do so? I saw there were some posts about generating rows to fill dates but I didn't see anything that was more related to this post and adding a loop for future dates.

Thanks in advance!

[danilang]

hi @OneandOnly13

Your formula doesn't stop because your condition has the 2 dates reversed.  it should be 

DateTimeDiff(Date, DateTimeAdd(DateTimeNow(),-1,"days"),"days") <=90

DateTimeDiff expects the later date as the first parameter.  With the params the way you have them, the difference is always negative

Dan

[JoeS]

Your are so close :-)

I think you need to change the order of the dates in your condition.

Edit - beaten to it ^

[OneandOnly13]

Thanks! I actually caught that a minute after posting. It now does only 90 days but it takes 90 days for each row. So example:

Activity Period Sub Period Forecasts.. Date

A            1              2             ######     11/26/2018

..........

A             1             2            ######       02/25/2019

A             1             3            ######        11/26/2018

[danilang]

@OneandOnly13

Are you saying that the 90 rows are already generated and you need to add a date value to each row??  If so, you can use a multi-row formula grouped on Activity.   The Generate Rows tool creates a set of new rows for each existing row in your dataset

Dan

[JoeS · Accepted solution]

@danilang and I are becoming a new tag team.

I think we both realised at the same time, and I agree, I think you'd be looking for a multi-row with this configuration:

[OneandOnly13 · Accepted solution]

Thank you @JoeS and @danilang ! The multi-row formula tool worked perfect!