New column for vertical formula

[carterfleming]

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?

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

[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 

[BS_THE_ANALYST]

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