community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Best way to calculate row by row as next row requires calculation in prior row

Atom

Capture.PNG

 

Hi there. Probably a simple question compared to some of the others I have seen on here, but I am trying to replicate something similar to the excel spreadsheet above. The expense, income, and net change data is there. However, I need to create the opening, generation(utilization), and closing columns.

 

The 2018 opening is the only number given. Generation/Utilization is the smaller of opening or absolute value of net change). Closing is open + generation/utilization. And then next year's opening is just prior row's closing.

 

Is there a simple way to do this? I've been trying to use multiple columns and multi-row/field formulas to no success. Thank you!

Alteryx Partner

I've whipped together a solution that first Transposes the data, then uses the Multi-row formula to generate the values, before CrossTabbing back to the original form.

 

I essentially look for the value of the "name" field that I want to update, and set the value based on position relative to the others. If your columns are not always in the same position, you may want to sort before applying the multirow.

 

You'll also probably need to tweak the row numbers a bit, as I didn't include the Expense or Income fields, but this will give you the gist of it.

 

 

MultirowMultifield.PNG

 

 

Let me know if this works for you, or you have any questions:

 

Alteryx Certified Partner

This sheet needs to be solved one year at a time. To achieve this calculation, I built an iterative macro using the [Year] field to control iterations. 

 

Check out my attached solution and let me know if you have any questions.

 

 

Atom

Thank you. This worked great.

Labels