Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

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

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!