1 of 1 people found this helpful
Have you tried the Forecast feature? Go to the Analytics tab and drag Forecast into the view (a window will appear as you drag; drop it onto the "Forecast" icon). Forecasts automatically materialize additional periods that are not in the source data. You can configure how the Forecast behaves by going to Analysis (menu) > Forecast > Forecast Options. (If you want to limit the forecast growth % to just the most recent year… I'm not real familiar with manipulating forecasts in Tableau, but I think you may need to create a calculated field that only returns sales for the years you want to include, and use that as the forecast, overlaid with the main field as a dual axis.)
Now… your requirement is a little more specific. You want to base the forecasted growth on just the last 2 years' worth of actual data. Tableau has its own way of forecasting, and it tends to rely on the totality of ACTUAL data as a basis. So you have to trick Tableau into only looking at the data you want. (And unfortunately, it's not as simple as using a calculated field to return NULL for the years you don't want to include — Tableau complains that it "doesn't have enough data" to do a forecast.)
Here's how I got Tableau to do what you want. Note that you can combine all these calculations into one gigantic calculated field, but I broke them out for readability.
1. Create a calculated field [Final year sales] which is an LOD expression to grab the last year's sales. (2016 in your example.)
2. Create a calculated field [Final year - 1 sales] which is an LOD expression to capture the sales for the year immediately before the last year. (2015 in your example.)
3. Create a calculated field [Growth rate] which will find the fixed growth rate to use.
4. Create a calculated field [Fake Sales] which basically projects the growth rate backwards through all the previous years. (Sort of like a reverse-CAGR calculation.)
5. Drag [Fake Sales] onto the Rows shelf as a second bar chart.
6. Turn on Forecasting. (Go to the Analytics tab and drag "Forecast" out onto the view.)
7. Click the SUM(Sales) pill on the Rows shelf, go to Forecast, and change it to "None".
8. Click the SUM(Fake Sales) pill on the Rows shelf, go to Forecast, and change it to "Trend".
9. Make the chart Dual Axis.
10. Remove "Measure Names" from the "All" tab of the Marks shelf.
11. Synchronize the axes. (NOTE: I had to change [Year sales] to a data format "Number (decimal)" rather than "Number (whole)" to enable the synchronize option.) Then turn off "Show Header" for the secondary axis. Voila!
Sample workbook attached (version 10.1.3).
Wow, thank you for the quick and very detailed answer! I think this works.
I don't fully understand the reverse CAGR calculation, I'll need to learn more about that, but it seems to give approximately the correct answer (there must be some small rounding error due to the nature of the power calc).
One of the main features I was looking for is a parameter control so that the user can control the growth % variable, so I replaced the [Growth Rate] with a parameter control and that seems to solve it! (attached)
Thank you very much, this will be extremely helpful in a lot of different projects, I was really struggling with something that seemed like it should be simple.