Iterative Problem

[caltang]

Hi all,

After much thought and advice, my problem is an iterative one. I have attached the sample data. The columns highlighted in yellow are already present, and the columns right of that are new columns to be generated in Alteryx.

Now, I will preface by saying that I am able to get the values of the following row (2nd row), but I have not been successful in generating the following rows (3rd, 4th, etc.). I cannot share my workflow due to confidentiality purposes, but I have made a dummy version of it that showcases my thinking of how to go about it. My main issue is how do I iterate it because there are 4 major moving parts. The average is dependent on the total units and total costs, which are then relying on the cost itself which involves a formula that calls the unit price from the previous row if it's null or 0, and calls the unit price to multiply with the number of units to get the cost. 

The concept follows like so:

A	Group 1
B	Group 1
C	Group 2
D	Group 2

.

Some calculations are applied onto Group 1, and is then fed back to the data set. Then, the next set of calculations should see the data like so:

A	Done
B	Group 2
C	Group 2
D	Group 3

This goes on until the records are finished. 

All help on this matter is appreciated, I've attached the excel file with the formulas in tact. Cheers!

[caltang]

I was given this link: https://community.alteryx.com/t5/Coventry-Northampton-UK/FIGURE-8-Macro-Example/td-p/917305 but I’m currently on my phone. If anyone wants to try it, this would help give some ideas as well I suppose!

Also serves as a reminder for me to check it out once I’m able to!

[caltang]

For more context, the first row of data seen in the sample will always be there for every product. So that first row is actually the values from the previous year - it basically crosses over and I’ve done that calculation already as it’s accumulated past data. Just that moving forward, I’m not sure how to iterate it with the four moving parts mentioned in my post. 

[Bren_Spill · Accepted solution]

@caltang - I hope I'm interpreting what you need correctly - please see a suggested solution attached. Thanks!

[caltang]

@Bren_Spill you did it! This is exactly what I am looking for. Thank you for helping my friend!

[Pang_Hee_Choy]

I think it should not a problem for you. 

I am posting this for the benefit of others who may view this post in the future.

if there have different products, please group by "product" column in both sample tools in the macro.

[Bren_Spill]

Glad I could help @caltang!

[caltang]

Good point. I’m actually batching this macro. Basically a batch macro running an iterative macro of each product. So good catch!