6 Replies Latest reply on Feb 14, 2019 11:54 PM by Kailash Negi

# CAGR calculation, based on year filter

Hello there,

I have a problem with the calculation of the CAGR (compounded annual growth rate) and therefore wanted to ask you for any help.

So far, the calculation of CAGR is based on two parameters, namely First Year and Last Year.

But as I want to have a dashboard where you can see TNS for several years and the related CAGR, I would like to have the CAGR depending on the chosen years for that the TNS is displayed.

Again:
- we have TNS for chosen years

- we need CAGR for exactly these years

example: TNS for 2010, 2013, 2014, 2015

What I want to see is the CAGR from 2010-2015

(so our First and Last Year should be dependent on the year filter)

I attached two screenshots of the CAGR calculation and the filters / parameters and a sample workbook (Tableau version 10.2).

-Alex

• ###### 1. Re: CAGR calculation, based on year filter

Hi

I spent some time with your question today your sample is attached also approached the problem 2 ways

See the screen shot below

way 1

Your date fields and parameters are set to Integers - so I left them that way for the first approach -

I broke the CAGR calculation into pieces

• Beginning value > if [Year]= [First Year] then ([TNS]) else 0 end
• ending value > if [Year]  =[Last Year] then ([TNS])  else 0 end
• Number of years > [Last Year]-[First Year]

and CAGR > ({ EXCLUDE [Year]:((sum([Ending Value]))/(sum([Beginning Value])))}) ^ (1/(([number of years])))-1

That results in a table like this and calculates the CAGR over the extreme dates (start and finish but allows you to select and show any years you choose

I'm an old marketing / engineer and I wanted to convert the Year into an actual date - makes it easier to plot and if you want to do some other date calculation you can or for martial year calcualtion

I just created an added field by duplicating the field and changing it to date the actual formula is shown below - but you (and I ) don't need to know it

Year Date>DATE(IF NOT ISNULL( DATEPARSE ( "yyyy", STR([Year]) )  ) THEN DATEPARSE ( "yyyy", STR([Year]) )   ELSEIF NOT ISNULL ( DATEPARSE ( "HHmm", STR([Year]) )  ) THEN DATEPARSE ( "HHmm", STR([Year]) )   ELSEIF NOT ISNULL ( DATEPARSE ( "mmss", STR([Year]) )  ) THEN DATEPARSE ( "mmss", STR([Year]) )   ELSEIF NOT ISNULL ( DATEPARSE ( "yyMM", STR([Year]) )  ) THEN DATEPARSE ( "yyMM", STR([Year]) )   ELSEIF NOT ISNULL ( DATEPARSE ( "ddMM", STR([Year]) )  ) THEN DATEPARSE ( "ddMM", STR([Year]) )   END)

Then I set up new parameters based on Dates and set the - pulled in the List from the Year Date Field and setting the format to YYYY

Then I had to re do the formulas above subbing in the Year Date for the Year field in the original

One additional change - Because we brought in Years and converted them to dates they are entered as Jan 1 - I assume (always dangerous)

That the period you want is the total year through Dec 31 - so the Number of year formula becomes >year([last date])-year([first date])+1

That will generate charts and graphs like shown below and allows you to change the date rang and the periods to show

Let me know if this helped

Jim

1 of 1 people found this helpful
• ###### 2. Re: CAGR calculation, based on year filter

Hi Jim,

at first, I would like to thank you very much.
This helps a lot understanding all the stuff and improving the formulas. (Especially for me as I am a real beginner in Tableau)

I also really like your second approach.

But frankly, it didn't solve my problem which, probably, I haven't explained detailed enough.

This is why I have attached a screenshot of my "aim dashboard":

In the end, it should look like this, having the following features:
For example

- Choosing the years 2010, 2011, 2017: it should depict the TNS of 2010, 2011, 2017 and the CAGR of 2010 - 2017

- Choosing the years 2015, 2016, 2017: it should depict the TNS of 2015, 2016, 2017 and the CAGR of 2015 - 2017

- Choosing the years 2011, 2013, 2015: it should depict the TNS of 2011, 2013, 2015 and the CAGR of 2011 - 2015

So, I would like to have only one selection (the years) which does all this for me :-)

Maybe, there is a possibility to make the first date and last date parameter dependent on the year filter?  So that I don't need to select the first and last date.

I would appreciate another great answer, thanks in advance for that and thank you very much again for your time, it was definitely not for nothing :-)

Alex

• ###### 3. Re: CAGR calculation, based on year filter

sorry

I don't know how to achieve what you need

Maybe someone else knows

Good luck

Jim

• ###### 4. Re: CAGR calculation, based on year filter

I think I managed to get a solution.

Due to your hints and all your calculations I found a new way to do all the calculations I need.

Here it is:

CAGR_beginning:

IF [Year] = {EXCLUDE [Year] : MIN([Year])} THEN ([TNS]) ELSE 0 END

CAGR_end:

IF [Year] = {EXCLUDE [Year] : MAX([Year])} THEN ([TNS]) ELSE 0 END

CAGR_numberofyears:
MAX([Year]) - MIN[(Year)]

CAGR:

(SUM([CAGR_end])/SUM([CAGR_beginning])) ^(1/(([CAGR_numberofyears])))-1

So, thanks again for yor help, it took me right to the solution :-)

Alex

1 of 1 people found this helpful
• ###### 5. Re: CAGR calculation, based on year filter

Excellent

Glad you got a solution and thanks for the badge - always appreciated

JIm

• ###### 6. Re: CAGR calculation, based on year filter

Was trying to solve a similar problem so your post helped me so thanks a lot. However I noticed that the number of years you are calculating for CAGR calculation is not correct.

It should be MAX([Year]) - MIN([Year]) + 1 because currently it's ignoring one year. Number of years for CAGR calculation of period 2014-2018 should actually be 5 years not 4.

Thanks!

Regards,

Kailash