New column for vertical formula

I need help figuring out how to translate the Excel function shown in the screenshot to Alteryx. I thought about using a Multi-Row Formula but I can't seem to get the expression right. Is there a Multi-Row expression for this or is there a better tool to use?

Preparation

Accepted answers

All comments

ShankerV

Hi @carterfleming

I see a common in you data where the Total row is null.

Hence use the below formula in Multi row formula tool.

IF Isnull([Total])
THEN tonumber([Row-1:Total])-tonumber([Row-2:Total])
ELSE null()
ENDIF

Many thanks

Shanker V

BS_THE_ANALYST

@carterfleming you can reference rows above or below the current row using the Multi-row formula. So you're definitely on the right lines!

Since you need to reference as far as 2 above your current row, I'd configure the Multi-row formula like this:

Here's an example how to configure it:

example multi row.yxmd

carterfleming

Apologies, the screenshot I originally gave was the Excel file I'm trying to replicate. Unfortunately in Alteryx, my data at this point in the workflow does not have null rows between it. I can't show the site names as it's company info but to the left of the Credits column here, there are two identifying rows and they are in the correct order for the calculation I need to perform. I need to subtract every second row from the row above it (i.e., (Row 2 - Row 1), (Row 4 - Row 3), (Row 6 - Row 5), etc.

BS_THE_ANALYST

@carterfleming Like this:

BS_THE_ANALYST

@carterfleming Then you can do this part:

Just taking every other, as per your request. This could probably be cleaner, but I don't have much time. Hopefully that's of use!
i.e. row2-row1 row4-row3

multirow_everyother.yxmd

BS_THE_ANALYST

@carterfleming okay I had to bring it together, it was bugging me 😂

multirow_everyother_new.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280