Multi-row formula, finding the rate of change for a given time.

[juan1]

Dear Alteryx Ninjas!

Hope all is well.

I wanted to see if you guys had any insights to enhance or improve my following methodology. I spent quite the time coming to this solution, and I am sure there are many ways to do achieve the same, and I am curious to see how you would solve it. 

Specifically, I want to calculate the rate of change for a country every month from January to April (I have aligned my data to show the first four months for all countries). The data looks like this:

Country	Month	Month numeric	Var	Delta
Argentina	JAN	01	3
Argentina	FEB	02	2
Argentina	MAR	03	-1
Argentina	APR	04	-11061

I used the following formula in the multi-row tool:

IF [Month numeric] = "01"
THEN [Var]
ELSEIF [Month numeric] = "02"
THEN [Row-1:Var] + [Var]
ELSEIF [Month numeric] = "03"
THEN [Row-1:Delta] + [Var]
ELSE [Row-1:Delta] + [Var]
ENDIF

These is my tool config:

1. 1. Create new field
2. 2. Group By: Country

This is my desired output:

Country	Month	Month numeric	Var	Delta
Argentina	JAN	01	3	3
Argentina	FEB	02	2	5
Argentina	MAR	03	-1	4
Argentina	APR	04	-11061	-11057

Thank you all in advance for your time and responses.

Best,

Juan1

[IraWatt · Accepted solution]

Hey @juan1,

Don't know if you want to hear this but I think the running total tool achieves this XD no formula/config required

If I was using a multi row through I think I would do this:

IF [Month numeric] = "01"
THEN [Var]
ELSE [Row-1:Delta] + [Var]
ENDIF

HTH,

Ira 

[juan1]

LOL! That is exactly what I wanted to hear!

I have a tendency for complicating things, and the obvious answer is not that obvious sometimes.

Thank you @IraWatt for your response. 

To all those reading this in the future, use the running total haha.

Cheers!

[IraWatt]

@juan1 hahaha no worries, I was working on the same problem myself recently only to be told about this tool! It doesn't get the credit it deserves ..