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!
Solved! Go to Solution.
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
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.
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!