Iterative Macro to Calculate Forecast and Closing Position

[JLiou]

I am very new to Iterative Macros and is struggling to get my first iterative macro I just wrote to calculate the monthly Opening Stock On Hand, Forecast Replenish Stock Order, and the Closing Stock On Hand due to the fact that the calculation of the 3 fields are inter dependent.  For example:

Loop 1 Logics:

Step 1:

Move Month zero's [Closing Stock On Hand] to Month 1's [Opening Stock On Hand]

Step 2:

Calculate Month 1's [Forecast Replenish Stock Order]

Step 3:

Calculate Month 1's [Closing Stock On Hand]

I have managed to get Loop1 to run if I run the macro as a workflow, but when I run the workflow with the macro attached it does not give me any output.  I have attached the macro logic as well as the macro and the workflow.  Can anyone help me?

Thanks!

[aatalai]

@JLiou have you thought about using the time series tools for forecasting? Or are you looking more to practice building macros?

[JLiou]

Now I'm thinking whether I need 2 macro input, one for all the data and a 2nd macro input for the Iterative output.  I saved the new macro as "Stock On Hand Iterative Macro v2" and attached it to the main workflow.  However, I am still getting no results from the macro.  Does anyone know why?

Thanks.

[Qiu · Accepted solution]

@JLiou 
For iterative macro, one point is that the data is all in and all out and get update for part of it for each loop.

In your 2 versions of macro, you only update the target rows and did not union back them to the rest of the stream.

I made a quick sample and hope it works for you.

[JLiou]

Right, the "all in and out" is the missing bit, they never tell you that in any tutorials.  However, by adding a union to add the processed row back to original incoming rows, I now have duplicates.  Do you think the best option to remove they duplicate and only select all those processed rows is to add a 'processed indicator' = Y and then add a filter tool after the macro to only select those that are Y?

[Qiu]

Not everything will be on the book or tutorials. 😁 Just like we can not grow only from school. 

I think it is bette you are aware what is the data row you are processing now.
In my example, I seperate the data stream with first Filter tool, the one row I am going to update and the rest.
After I update the one needed to be updated, I union back to the rest of steam.

[JLiou]

@Qiu ,

I can't seem to find the answer anywhere on iterative macro but I wanted to find out whether all columns/fields used 'within' the macro must also be present in the Macro Input and Macro Output?  For example, if I have a "DayCount" column in the Macro Input & Output but "within" the macro I use a Formula Tool to create a new column called "HoursCount" (ie [DayCount] * 24) and I want to use HoursCount in every loop of the macro - it is necessary for me to have HoursCount as a column/field in the Macro Input & Output?

Thanks in advance.