If you click on "Edit Table Calculation..." and click on "Customize..." in the Table Calculation window you can assign a name to the calculated field.
Hope this helps...
Unfortunately, I tried this approach. I am a neophyte to Tableau formula construction. The generated formula does not produce the same results as the Quick Table Calculation - even when everything else is equal. It does not even produce 0% in the start year.
The Tableau generated formula from the Edit Table Calculation is:
- POWER(ZN(SUM([Amount]))/LOOKUP(ZN(SUM([Amount])), FIRST()),ZN(1/(INDEX()-1))) - 1
I see the POWER formula embedded in the calculation. I understand ZN addresses the NULL values. I do not understand the INDEX reference.
I am working from the assumption that the formula to calculate a CAGR for 2013-2019 growth would be as follows:
- POWER(SUM()/SUM()^(1/6) - 1
What am I missing?
Thanks for your assistance.
The INDEX reference is basically the offset in your array of values. So, if your years are 2013 to 2019, thinking of this as an array of seven values, the values of INDEX() will be 1 for the 2013 value, 2 for the 2014 value ... 7 for the 2019 value.
Your formula POWER(SUM(/SUM()^(1/6) - 1 is the value of the formula for one specific value in this array. The "(INDEX()-1)" term in the Quick Table formula would equal 6 when it calculates the value for the year 2019 (the seventh value in the array), which corresponds to the denominator in the "1/6" portion of your explicit formula for the year 2019.
The FIRST() parameter in the LOOKUP function gets the first value in the array for the denominator, which corresponds to your "/SUM(".
I don't see why the Quick table formula wouldn't produce what your expecting. Can you perhaps post a small workbook with some data and your formula?
Thank you for your assistance. It is greatly appreciated.
I am failing at the logic structure of the formulas. The attached image depicts what I want to generate.
I need a Measure that has a CAGR for each Budget Line (Dimension). I then need a Measure that generates the CAGR for each Market Segment (Dimension) - i.e., a group of Budget Lines.
The Tableau generated CAGR calculation only works when I recreate a Table with a Dimension vs. Year. I need the results to be the Measure, not the calculation - just like each Budget Line has an amount associated with it for each given year of the forecast.
I hope that makes sense.
Is there an image to be uploaded? I don't see one anywhere.
Sorry, here is an image that depicts what I am trying to do. It's not the table calculation that I want. I want the results of the table calculation to be used as a Measure - in total (budget line) and in sum as a subset (market segment).
I hope this helps, because I am stuck.
OK, I see what you're trying to do. It's more complex due to the 2-step nature - first calculate the two sets of CAGR values, then use those (new) sets of values in a scatterplot. Offhand I don't know if this can be done.
In a sense this is similar to a question I had posted not that long ago, and a great answer was provided by Joshua Milligan. He called my question a form of "slicing by aggregate', and if you view the calculating of the CAGRs as a form of aggregation then it may apply to your situation too. Have a look at his site's article on Slicing by Aggregate | VizPainter Perhaps something there will be help.