2 Replies Latest reply on Apr 4, 2016 5:00 AM by Sabine Honer

    issue with CAGR calculation

    Sabine Honer

      Hi All,

       

      we have create a CAGR calculation by using Lookup():

       

      (LOOKUP(SUM([Month Totals]),LAST())/LOOKUP(SUM([Month Totals]),FIRST()))^(1/(SIZE()-1))-1

       

      Formula will only work, if all years are visible.

      Now I am searching for a way, how to adjust the formula to be able to display only a few years.

      Can I refer to value for 2012 and 2016 and also lookup the specific years instead of using SIze() within formula?

       

       

      Thank you very much in advance for any hints.

       

      Best regards

      Sabine

        • 1. Re: issue with CAGR calculation
          Simon Runc

          hi Sabine,

           

          So I think what your asking here is to be able to filter the Viz (on year) but not change the underlying CGAR calculation (having it always based on all the years in the data)...let me know if I've mis-understood (...certainly wouldn't be the first time!)

           

          I have a couple of solutions for you, both exploiting different parts of Tableau's Order of Filtering Operations (it's a deep subject, but in short different filter and calculation types get calculated at different parts of Tableau's calculation pipeline...here's a link to a great discussion on the subject

          Evolution of the Order of Operations Diagram

          and I'd also check out Alexander's blog

          Vizible Difference: Evolution of the Order of Operations Diagram

           

          So the first (and easiest way) is to use the fact that Table Calc filters are calculated last, so has the affect of filtering the Viz, but not the underlying data. So to make the year a Table Calc filter I use the following formula

          LOOKUP(MIN([Year]),0)

          I bring this into the filter shelf and expose it and voila!!

           

          The 2nd way is  to create the CGAR calculation as a FIXED LoD...you can see this gets calculated before any dimension filterers are applied, so again the CAGR calculation is calculated before any years are filtered out. The advantage of this method is that you can apply a regular filter across worksheets, which you can't with a Table Calc filter. The disadvantage (over the Table Calc method) is that there is a performance cost to using LoDs...although this is only really noticable on large data sets)

           

          The calculation as an LOD would be

           

          ({SUM(IIF([Year]={MAX([Year])}, [Month Totals],0))}

          /

          {SUM(IIF([Year]={MIN([Year])}, [Month Totals],0))})^(1/({MIN({COUNTD([Year])})}-1))-1

           

          and in the 'How each part works' tab you can see what each element is doing.

           

          Hope that helps.

          1 of 1 people found this helpful
          • 2. Re: issue with CAGR calculation
            Sabine Honer

            Hi Simon,

             

            sorry for my late reply.

            Thank you very much for the answer. It is awesome.

             

            Best regards

            Sabine