Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-row Formulas that Reference Each Other Over Several Rows

samiamanactuary
5 - Atom

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):

 

A1B1C1
A2B2C2
A3B3C3

 

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!

3 REPLIES 3
NicoleJohnson
ACE Emeritus
ACE Emeritus

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
5 - Atom

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

sann0638
8 - Asteroid

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!

Labels