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

[EveM]

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:

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

[MichalM]

Could you please share your data as a file?

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

[MichalM · Accepted solution]

@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

 

[EveM]

@MichalM Here is the input thanks!

[MichalM]

Attached is the workflow with your data