Calculate the difference between row values in a column and output in a different column

Hello everyone,

I am trying to calculate the difference between records in a column and output the result in a new column. The formula needs to compare the records having the same Entity and the same Code. The calculation is basically :

If ENTITY=ENTITY and CODE=CODE then diff= amount of current month - amount of last month.

I just don't know how to write the formula for the calculation of the difference. Does anyone have an idea? Thanks!

Here is the input :

and what the output shoud look like:

Output

Tips and Tricks

Custom Tools

Date Time

Interface Tools

Accepted answers

MichalM

@EveM

I assume you want the result to say N/A (NULL) if there's no value for the previous period - first occurrence of the group. In this case can make the below change in the setting and update @rafalolbert's formula to

IF IsNull([Row-1:amount]) THEN Null()
ELSE [amount]-[Row-1:amount]
ENDIF

All comments

MichalM

@EveM

Have a look at the Multi-row formula. This will allow you to perform calculations across multiple rows. Using the group-by function by Entity and Code will allow you to restart the calculation with every new pair of Entity and Code.

EveM

@MichalM Yes that's indeed what I've been trying to do, but so far it did work. It always gives a difference of 0..

rafalolbert

Hi @EveM,

I suggest to sort by: entity, code and date and use Multi-Row Formula as below:

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

workflow.yxmd

MichalM

@EveM

IF IsNull([Row-1:amount]) THEN Null()
ELSE [amount]-[Row-1:amount]
ENDIF

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335