2 Replies Latest reply on Jan 23, 2017 12:03 PM by David Kauffman

    How to Project Year Over Year Growth With Parameter Control

    David Kauffman

      I have searched all over for what seems like a simple answer. I have sales data by year for x number of years. I want to make a visualization that includes all the historic data but also shows the future YOY growth, say 10 years, assuming that the growth rate for those 10 years is controlled by a parameter (say 1-15%).

      My years end at 2016 (obviously), so the question is how do I generate the next x years, as well as have the projected sales $ for those years be a calculation that is based on the last year of actual data (I don't want a forecast based on the entirety of the data - I want it to only show what is the sales amount for the following x years based on a x% YOY growth in relation to the previous year, but starting at the last year of actual data - 2016, so that 2017 would be actual 2016 sales x 1.08, 2018 would be calculated 2017 result x 1.08, etc.

      Thanks!

        • 1. Re: How to Project Year Over Year Growth With Parameter Control
          Jamieson Christian

          David,

           

          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).

          1 of 1 people found this helpful
          • 2. Re: How to Project Year Over Year Growth With Parameter Control
            David Kauffman

            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.