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

issue with CAGR calculation

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

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

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

Hi Simon,

sorry for my late reply.

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

Best regards

Sabine