Multi-row Formulas that Reference Each Other Over Several Rows

[samiamanactuary]

Sorry if the subject isn't clear enough. I have a series of Excel formulas that I want to translate into Alteryx and for much of what I'm calculating the multi-row formula will do the job. I'm running into problems when I need to reference across formulas and time periods, so I can't just work left to right. I've attached an example in Excel that does exactly what I want for a subset of my workflow (don't worry about the reasoning behind the formulas; I just love to put myself through pain at work). Here's a basic example of the calculation issue (more clear and detailed in the attachment):

A1	B1	C1
A2	B2	C2
A3	B3	C3

B2 = If(C1 > 0, A2/C1, 0)

C2 = C1 + A2 + B2

So B is dependent on the current row of A and the prior row of C, but C is also dependent on the current row of A and B, as well as the prior row of itself. This isn't a great example, but in effect the columns are dependent on each other while also not being circular references (again, the attachment would be clearer than my sub-par example here).

I've tried using the CReW dynamic formula tool, but I can't get it to recognize a reference to the prior row in the formula expression (i.e. [Row-1:Charges]). I will take any reasonable ideas that could accomplish the task with decent speed (doing this on 10's of millions of rows). Thanks for any and everything!

[NicoleJohnson · Accepted solution]

Yikes, this was a good one!! Does seem to be possible however - you just need a series of IF statement-heavy Multi-Row Formula tools (plus a few other Formula tools, Select or Multi-Field tools, etc.)

The trick was that you basically had to recreate the formulas needed for the Accumulated Uncollected Charges within that formula, since you can't create the Contribution of Accumulated Uncollected Charges, Uncollected Charges, and Release on Decremented Charges formulas without the Accumulated Uncollected Charges formula already having been calculated for the row prior... since there's not a Multi-Row-and-Field tool, doesn't look like there is a way to accomplish this all in one tool (unless one of the real Alteryx wizards on here has some fancy macro/tool/etc. that does this??) However, difficult does not mean impossible. So it isn't pretty, but it does get you the same results!

Hope that helps get you started down the right path with your solution!

Cheers,

NJ

[samiamanactuary]

This is an awesome, fast reply! It pulled off exactly what I needed. Thank you so much, Nicole!

[sann0638]

I had a similar problem to this one, and got round it by duplicating the data and calling it "before" and "after", which made the calculations a bit easier.  Was considering it wasn't possible but Nicole's answer encouraged me to keep trying!