4 Replies Latest reply on Oct 23, 2014 7:18 AM by Eric Corbin

    Parameter Driven Forecasting

    Eric Corbin

      This workbook is simple in principle, but a challenge to produce.  The blue bars represent operating expenses of a property over time.  The orange bar is dynamically controlled by the parameters at the top of the dashboard. I would like to change the (What If) parameters and have Tableau forecast the (blue) corresponding years in the future while adding the proportionate (orange) capital.

      For Example, if you type 510K into capital and 2 years into the term you should expect to see 255K in 2014 and a forecast of an additional 255K (orange bar) in 2015 above and beyond the forecasted 2015 operating number.

       

      I hope that you can help.

      Thanks

      Eric 

        • 1. Re: Parameter Driven Forecasting
          Jonathan Drummey

          Hi Eric,

           

          Are you still looking for help on this?

           

          Jonathan

          • 2. Re: Parameter Driven Forecasting
            Eric Corbin

            Hi Jonathan,

             

            Yes I am.

             

            Thank you

            Eric

             

             

             

            Sent with Good (www.good.com)

            • 3. Re: Parameter Driven Forecasting
              Jonathan Drummey

              You're not going to be able to use Tableau's built-in forecasting with the view or data that you've provided, for a three reasons:

               

              1) There's not enough detail in the data for Tableau to compute a forecast, I'm not sure of the minimum number of date increments but it's definitely more than 4.

              2) When a view includes table calculations (which yours does), Tableau won't compute a forecast.

              3) We don't have access to the forecasted to either dynamically set a number of them nor to append another measure just to the forecasted results.

               

              Therefore, you're going to have to define your own forecasting algorithm and set up the data to take advantage of that. Based on what you'd posted, I'd add a column to the data that has the max reporting date (if you have multiple subgroupings/aggregations of the data, then you might need multiple columns for different aggregation levels), and add a "Source" column to identify the original data, then add a row to the data for every distinct grouping that you'll be filtering for (in your sample data you'd need two rows, one for each ORFAC). This data will have the max date for the data, and a value for "padding" in the padding column. This can be a static view in Excel, or something built more dynamically either through formulae in Excel or Custom SQL.

               

              Then you can create a calculated field for the "Period for Forecast" that is something like

              DATE(CASE [Source]

                  WHEN 1 THEN [Period] //raw data

                  WHEN 2 THEN DATEADD('year',[Expiration Extended (Years)]-1,DATEADD('year',[Max Period]-2000,#1/1/2000#)) //padded data

              END)

               

              Once you bring that into a view, turn on Show Missing Values for that date pill and Tableau will automatically pad out the dates between the Max Date and your extended date. Note that the DATEADD('year'... only takes integers, and you'd set up a decimal Expiration Extended parameter, so that calc may need to be modified if you want to extend by fractions of years.

               

              Then your forecasting algorithm and calc for adding capital can be built using table calcs. If you need help with that, feel free to post again here and I'll check it out.

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Parameter Driven Forecasting
                Eric Corbin

                Jonathan,

                Your explanation is clear, but I don’t think that I completely understand how to change the source data according to your suggestion.  If you have a moment would you mind showing me what you mean.