Iterative Macro to Calculate Forecast and Closing Position

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!

Stock on hand iterative calculation example.xlsx

Stock On Hand Iterative Macro.yxmc

Calculate Stock on Hand.yxmd

Iterative Macro

Accepted answers

Qiu

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

Calculate Stock on Hand-r1.yxzp

All comments

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.

Stock On Hand Iterative Macro v2.yxmc

Calculate Stock on Hand.yxmd

Qiu

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

Calculate Stock on Hand-r1.yxzp

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703