
1. Re: CAGR calculation, based on year filter
Jim Dehner Apr 10, 2017 1:59 PM (in response to Alexander Pinzger)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

Sample.twbx 36.0 KB

2. Re: CAGR calculation, based on year filter
Alexander Pinzger Apr 10, 2017 11:41 PM (in response to Jim Dehner)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
Jim Dehner Apr 11, 2017 5:51 AM (in response to Alexander Pinzger)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
Alexander Pinzger Apr 13, 2017 2:57 AM (in response to Jim Dehner)1 of 1 people found this helpfulI 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

5. Re: CAGR calculation, based on year filter
Jim Dehner Apr 13, 2017 5:56 AM (in response to Alexander Pinzger)Excellent
Glad you got a solution and thanks for the badge  always appreciated
JIm

6. Re: CAGR calculation, based on year filter
Kailash Negi Feb 14, 2019 11:54 PM (in response to Alexander Pinzger)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 20142018 should actually be 5 years not 4.
Thanks!
Regards,
Kailash