4 Replies Latest reply on Apr 17, 2013 11:13 PM by Daniel Huedig

# How can I create a CAGR with data set up with the time period set up as a column?

I'm hoping someone might be able to help me write a formula to calculate CAGR.   The data is set up in the format below, and the goal is to create a calculated field where the output = CAGR % for 2007 - 2010.   It might be important to note that the data set contains more years of data than just 2007 to 2010, but i'm interested in creating CAGRs for fixed windows of time.  We would sometimes display this CAGR figure at the school level, and sometimes in aggregate.   Any ideas about how to write this formula are very much appreciated!

 School Year % students A 2007 40 A 2008 45 A 2009 46 A 2010 49 B 2007 50 B 2008 51 B 2009 52 B 2010 52 C 2007 60 C 2008 64 C 2009 65 C 2010 67
• ###### 1. Re: How can I create a CAGR with data set up with the time period set up as a column?

What is CAGR?

• ###### 2. Re: How can I create a CAGR with data set up with the time period set up as a column?

Alex, CAGR is the Compound Annual Growth Rate. (http://www.investopedia.com/terms/c/cagr.asp#axzz1r5DUzolI)

Catherine,

I have attached a sample workbook using the data provided above. The calculation looks like the following:

((LOOKUP(sum([% Students]), last() )/LOOKUP(sum([% Students]), first() ))^(1/total((count(datepart('year', [Year]))))))-1

Once it is placed on the view, make sure to right click on it and select Compute Using-->Pane(Down).

Hope this helps!

-Tracy

• ###### 3. Re: How can I create a CAGR with data set up with the time period set up as a column?

I believe that the formula is giving out a wrong result and should be

((LOOKUP(sum([% Students]), last() )/LOOKUP(sum([% Students]), first() ))^(1/total((count(datepart('year', [Year]))-1))))-1

There is one more '-1' to be added as the CAGR only looks at complete periods, and here there are only three (the 2007 datapoint marks the closing of 2007 and actually equals the opening one for 2008, and 2008, 2009 and 2010 are three complete periods.

In any case, I cannot get the formula to work in Tableau v8, it is always complaining about the second part of the formula, specifically the "datepart" portion. It says "datapart is being called with (string, string), did you mean (string, datetime)".