Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music
SOLVED

Divide by previous year (or any category)

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

 

Highlighted
12 - Quasar
12 - Quasar

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:

Highlighted
12 - Quasar
12 - Quasar

Lol @Jonathan-Sherman  we are on a roll!  

 

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Labels