Alteryx Designer Discussions

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

Time-Series: Taking Last Record And Calculating CAGR X Periods Back

Hi,

 

I have data that is refreshed every quarter. So there is a new RecordID added every quarter along with index data when our db refreshes every three months.

 

What I am trying to do is take the most recent (last) data point of the index and go back 15 years or 61 periods to calculate a CAGR (compounded annual growth rate) like the the Excel example which is for 15-years (2019.4 versus 2014.4).

 

So, I need to be able to do this when the next data set comes in, our db is refreshed and a new RecordID is created for example 2020.1 versus 2015.1.

 

I have tried Multi-Row and Sample (last), but not getting the results in the attached Excel where I have provided the exponential CAGR calc I am trying to achieve. 

 

Thanks for any help/ideas! 

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @joe_de_foleschampe ,

 

I was thinking about your solution and I thought give you something completer that maybe you don't need at all lol

But it does the job!

 

Best,

Fernando Vizcaino

17 - Castor
17 - Castor

Hi @joe_de_foleschampe 

 

Good solution from @fmvizcaino,  Here are 2 more ways to tackle this

w.png

 

The top branch uses a multi-row tool, configured to look 60 rows back with (Pow(([Index Value]/[Row-60:Index Value]),1/15)-1)*100.  The bottom branch creates a new [Index Period] column with a start value of -59 and performs a self join on [Index Period]=[RecordID] and a similar formula.

 

The final few rows of output look like this.

r.png

 

Dan

 

 

Excellent! Thank you so much!

Hi,

 

Yes, thank you and @fmvizcaino for your solutions! 

 

I see where all of the solutions can help me crack this and other time-series challenges!

 

Thanks so much and really appreciate all of your insight and help!

 

Have a nice day!

Also, thanks for the syntax on the CAGR calc with Pow, which is helping me with Excel <> Alteryx conversion of functions/arguments!

Labels