Divide by previous year (or any category)

[FabianSardagna]

Hello, 

I have data that looks like this:

Year	Country	Cases
2018	Germany	15
2019	Germany	6
2020	Germany	7
2018	Brazil	8
2019	Brazil	5
2020	Brazil	2

What I wanto to do is calculate Cases - Cases from previous year. So the output should look like this:

Year	Country	Cases	Total
2018	Germany	15	15
2019	Germany	6	-9
2020	Germany	7	1
2018	Brazil	8	8
2019	Brazil	5	-3
2020	Brazil	2	-3

Anyone can help? Thanks!!

[Jonathan-Sherman · Accepted solution]

Hi @FabianSardagna 

You'll want to use a multirow formula to refer to the previous row:

IF IsEmpty([Row-1:Cases]) THEN [Cases] ELSE [Cases] - [Row-1:Cases] ENDIF

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[ggruccio]

Hi @FabianSardagna,

Looks like you want the multi-row formula tool.  I added some logic to make the total the number of cases in year 1 and to sort the end results so that Germany would come before Brazil similar to your output.

See workflow below:

[ggruccio]

Lol @Jonathan-Sherman  we are on a roll!  

Got me again by a few seconds...Awesome work though!!!!

[Jonathan-Sherman]

We certainly are @ggruccio! (Made a slight mistake which I've just seen and fixed!)