Formula Help or Maybe an Iterative Macro?

[Fischer]

Hello! 

I'm new to Alteryx and have come across a calculation issue that I just can't see to resolve.  Basically I am taking prior month inventory amounts as a starting point to project inventory levels for the following month.  Each new projected month uses the prior month as a starting point for the calculation.

I have been able to complete the following:

1.  Create a new column name "Sum Prior Month Current Inv" to show prior months "Sum Current Total Inventory Amt - As Of"

2.  Multiply the "Sum Prior Month Current Inv" by "Sum FCST Inv Adjustment" to create the "Sum Value"

3.  Take the "Sum Value" plus "Sum Prior Month Current Inv" to create "Predictive Current Inventory"

4.  Place the "Predictive Current Inventory" result into the "Sum current total inventory Amt  - As Of"

Now I'd like to repeat this same process till all the "Null" records for "Sum Current Total Inventory Amt - As Of" are populated; thereby; creating a monthly inventory profile into the future.

I think I might need a macro???  Thanks for any help.

Cheers, Caroline.

[jdunkerley79 · Accepted solution]

I would guess you could do it in a single multi-row formula tool. I think you could make the Predictive Current Value with the expression:

IF Not IsNull([Sum Current Total Inventory Amt - As Of]) THEN
	[Sum Current Total Inventory Amt - As Of]
ELSE
	[Row-1:Predictive Current Inv] * (1 + [Sum FCST Inv Adjustment])
ENDIF

  Having populated this column  you could then reverse out the other columns using Multi-Row formula tools

If you can post some data and expected answers happy to adjust formula to work correctly

Sample attached based on screenshot and above expression

[Fischer]

Thank you this worked perfectly!  I really appreciated the quick response.