Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

joe_de_foleschampe
7 - Meteor

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! 

 

5 REPLIES 5
fmvizcaino
17 - Castor
17 - Castor

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

danilang
19 - Altair
19 - Altair

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

 

 

joe_de_foleschampe
7 - Meteor

Excellent! Thank you so much!

joe_de_foleschampe
7 - Meteor

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!

joe_de_foleschampe
7 - Meteor

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

Labels