Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

OneandOnly13
8 - Asteroid

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!

6 REPLIES 6
danilang
19 - Altair
19 - Altair

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
Alteryx Alumni (Retired)

Your are so close :-)

 

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

 

Edit - beaten to it ^

OneandOnly13
8 - Asteroid

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
19 - Altair
19 - Altair

@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
Alteryx Alumni (Retired)

@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

OneandOnly13
8 - Asteroid

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

Labels