
1. Re: issue with CAGR calculation
Simon Runc Mar 26, 2016 12:07 PM (in response to Sabine Honer)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 misunderstood (...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.

cagr with filtering SR.twbx 299.5 KB


2. Re: issue with CAGR calculation
Sabine Honer Apr 4, 2016 5:00 AM (in response to Simon Runc)Hi Simon,
sorry for my late reply.
Thank you very much for the answer. It is awesome.
Best regards
Sabine