Divide by previous year (or any category)

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!!

Tips and Tricks

Transformation

Help

Tableau

Expression

Time Series

Accepted answers

Jonathan-Sherman

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

20200519 - MultirowFormula.yxmd

All comments

Jonathan-Sherman

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

20200519 - MultirowFormula.yxmd

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:

multirowlogic.yxmd

ggruccio

Lol @Jonathan-Sherman we are on a roll!

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703