5 Replies Latest reply on Apr 13, 2017 5:56 AM by Jim Dehner

    CAGR calculation, based on year filter

    Alexander Pinzger

      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).

       

      Thank you very much in advance for your hints.

       

      -Alex

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

          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
            Alexander Pinzger

            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":

            2017-04-11_08h09_23.png

             

            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

              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

                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
                  Jim Dehner

                  Excellent

                   

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

                  JIm