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!

[BRRLL99]

Hi

You can try this formula for each column

use multi row formula : IIF([Forecast Type] = 'SL', [Row-2:2023] + [Row-1:2023], tostring('Null'))
this approach will become static 

you need to apply same formula all columns using different multi row formulas 

[danilang · Accepted solution]

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