community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE
Announcement | We'll be doing maintenance between pm 4- 6pm MT, which may impact your experience. Thanks for your patience as we work on improving the community!
SOLVED

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

Highlighted
Meteoroid

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:

 

image.png 

 

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!

Quasar

hi @zhuff13

 

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

Alteryx
Alteryx

Your are so close :-)

 

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

 

Edit - beaten to it ^

Meteoroid

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

Quasar

@zhuff13

 

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

Alteryx
Alteryx

@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:

 

Multi-row Date.png

Meteoroid

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

Labels