Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Divide by previous year (or any category)

FabianSardagna
5 - Atom

Hello, 

 

I have data that looks like this:

 

YearCountryCases
2018Germany15
2019Germany6
2020Germany7
2018Brazil8
2019Brazil5
2020Brazil2

 

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

 

YearCountryCasesTotal
2018Germany1515
2019Germany6-9
2020Germany71
2018Brazil88
2019Brazil5-3
2020Brazil2-3

 

 

Anyone can help? Thanks!!

4 REPLIES 4
Jonathan-Sherman
15 - Aurora
15 - Aurora

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

 

Jonathan-Sherman_0-1589913095145.png

 

 

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
ACE Emeritus
ACE Emeritus

ggruccio_0-1589912772803.png

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
ACE Emeritus
ACE Emeritus

Lol @Jonathan-Sherman  we are on a roll!  

 

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

Jonathan-Sherman
15 - Aurora
15 - Aurora

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

Labels