Alteryx Designer Discussions

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

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



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!



Fernando Vizcaino

17 - Castor
17 - Castor

Hi @joe_de_foleschampe 


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



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.






Excellent! Thank you so much!



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!