Adding Together Rows

AlteryxLearner23

Hi,

On the attached sheet, I'm trying to create a formula where if column 'Forecast Type' is 'SL', add together the two rows above it.

So, for example, Row 4 is SL and zero for 2023. Therefore, I want to add together the two rows above it, making the new value 144 for 2023 and 500 for 2026.

I want the same logic to apply to all SL rows, which, in this case, is row 4 and row 7.

Thanks!

Calculation.xlsx

danilang

Hi @AlteryxLearner23 

As @BRRLL99 pointed out, adding a Multi-row Formula tool per year is static.  Once you roll over to 2024, you'll have to modify the workflow to handle 2029 data.

Here's a dynamic way to perform this

Start by removing the null rows, add a row ID and transpose all the Year columns.  Number the columns within each group resulting year rows and sort by Property ID, Column and Record ID.  Use a Multi-row Formula tool to sum the previous 2 values if the Type=SL.  Cross tab to get the original structure and use a Dynamic Rename to remove the underscores that the Cross tab tool inserts.

Dan

Residuals.yxzp