Unable to dynamically change the values in a field, even after using an iterative macro!

[siddharths6]

Hello,

I'm trying to work on some data that has fields which require numbers from subsequently calculated fields. For example, in the data shown below:

Closing Amount = [Row-1: Closing Amount] + [Purchase Value] + [Sales Costs] + [Amortization Income]

Sales Cost = Cost of Sales per Unit * Units

Cost of Sales per Unit = Closing Amount/Cumulative Units

Avg Price = if [Row-1:Unique ID] = [Unique ID]
and [Transaction Type] = "-" then [Row-1:Closing Amount]/[Cumulative Units]
elseif [Row-1:Unique ID] = [Unique ID]
and [Transaction Type] = "Purchase" then ([Row-1:Closing Amount] + [Deal Value])/[Cumulative Units]
elseif [Row-1:Unique ID] = [Unique ID]
and [Transaction Type] = "Sale" then ([Row-1:Closing Amount] + [Sales Cost])/[Cumulative Units]
else [Deal Value]/[Cumulative Units]

As you can see by the formulas above, the fields are interdependent and i need the values to update dynamically when the vaues in the previous or subsequent fields change. For example, if Closing Amount increases after Amortization Income is added, i need the Cost of Sales per Unit to increase accordingly, and similiarly the Sales Cost increases too.

Would this function be possible? How could i go about building it?

[mst3k]

i think you could just string a few of the tools together? looks like you've got the right idea for the multi row tool formulas. so do them one at a time, and insert a regular formula tool in between to utilize the previously calculated multi row value

[DawnDuong]

hi @siddharths6 

The Multi-Row tool will be able to solve this case for you. A great place to get familiar with this tool (if you have not already looked) is the Knowledge Base article below:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Multi-Row-Formula/ta-p...

Multi-Row tool is great for solving "roll-forward" type of problem where the t values depending on one or more t-m values.

Dawn.