5 Replies Latest reply on Aug 10, 2011 11:26 AM by harshal tannu

    Predictive Analytical Modelling

    harshal tannu

      I am new to Tableau and am trying to build a predictive analytical dashboard for effective Resource Allocation. I went through various forums posted on Tableau support, but was not able to find a fix solution for my dashboard requirement. Here is brief description about the requirements and target audience.

       

      Requirements –

      Officers / departments work on individual tasks represented by a Document Number. In a year, officer would have worked on ‘x’ number of documents for ‘y’ number of hours. This can help us compute average ‘hours / documents’ required for a officer to complete a task. Since a officer is assigned to a ‘Officer Class’, ‘Category’, ‘Service Center’ etc, aggregating the hours at these individual levels would represent amount of work load at different levels. Which can be used to calculate the ‘FTE’ requirement for that level. Dashboard I am building will look at this historical data, present in organization database, and will predict the workload and FTE requirement at levels mentioned above (Service Center, etc).

       

      Audience –

      This dashboard will be used at all the levels, Directors / Managers / Officers, to have a look at historical workload data. As described above, this dashboard will also provide a forecast of workload and help individual to manage resources efficiently within various departments.

       

      Design –

      Dashboard should first display the historical data in tabular form. Rows will have different departments and columns will show ‘Document Count’, ‘Hours per Document’, ‘Total Hours’, ‘FTE’ spread across past years. Last column can show forecasted values for forthcoming year. I am thinking of having this forecasted value to be an editable field, so that manager can change the ‘Document Count’ (estimated workload) and see what will be the ‘FTE’ requirement for his department. Something like scenario analysis.

       

      I was able to build a dashboard which presented me historical data in tabular form. However, I am still not able to have a forecasted value (computed in Tableau itself) based on past years workload. Also I am still to figure out how I will be able to provide an editable field to change the forecasted value (once I compute it).

       

      Any help will be greatly appreciated. Thanks!

        • 1. Re: Predictive Analytical Modelling
          Dimitri.B

          What formula or algorithm are you planning to use for calculating forecast values? Tableau does not have a built-in forecasting mechanism (yet).

          You can probably do it by joining a frame table with past and future dates with your data table (data blending), and using your preferred formulae in calculated fields to get forecast.

          As for the editable field - you can use parameters to add a coefficient or factor to the calculated forecast. Factor of 1 would mean no change, other values will multiply/divide the forecast by selected factor.

          If you can attach sample workbook with data, others can have a go at it.

          • 2. Re: Predictive Analytical Modelling
            harshal tannu

            Thanks Dimitri.

             

            I am planning to use 'slope - intercept' formula for finding out the forecasted value. Thus my forecasted value will be according to the linear trend of the data set. However, I do want to take this forecasting to next step by using 'polynomial' formula of second degree which will give me a better forecast, depending upon the latest trend.

             

            I have attached a sample worksheet along with this note. Here is a short description.

            1. Worksheet - dataset, contains the data extract with historical data.

            2. Worksheet - pivot table, contains aggregated data for individual departments. Service Center is the top most hierarchy with Officer Class, Activity Type and Group Code falling below it. This data now represent document count, hours spent, hours / document and FTE for individual departments spread across the years.

             

            Using this historical data I plan to do forecast for individual departments for year 2012. What I want to acchieve is to have a forecasted value for Document Count (as default) for year 2012 and then user will be able to modify this forecasted value using slider / manual input. Using this modified value then FTE can be calculate by simple formula as - Document Count / (hours in a year). FTE will then be represented in a graphical way. I was able to design a sample worksheet (without the forecasted values), and have attached it below.

            http://public.tableausoftware.com/views/ResourceAllocation-Sample/Dashboard?:embed=yes&:toolbar=yes&:tabs=no

             

            Thanks for the help in advance.

             

            Cheers,

            Harshal.

            • 3. Re: Predictive Analytical Modelling
              Dimitri.B

              Harshal, I had a look and, based on the nature of your data, it seems that the simplest option is to do forecasting in Excel (using Excel's built-in FORECAST function) and handing prepared data to Tableau. I have attached two files, data and workbook, as examples.

              Note that data for 2011 is incomplete, as we are still in August, so I assumed it is up to July but it looks like there are even fewer months of 2011 in your data. So it needs to be annualised for the forecast to work.

              The workbook has a parameter to adjust the forecast, if required.

              • 4. Re: Predictive Analytical Modelling
                harshal tannu

                This is fantastic Dimitri. Yeah, you are right, data for the year 2011 is not up to the date. Actual dashboard will consider only the data that is annulaised till last year.

                 

                For forecasting, I used following approaches -

                1. FORECAST function: provides a linear forecasting model. This has limitations in terms of considering the transient data peaks

                2. TREND function: considers the trend of data. This has limitation about putting too much weight on the recent year data points

                3. AVERAGE function: provides just the average over the years. Not a good approach

                4. Weighted Average: user defined weights on the past year data points

                 

                Since the last approach has more flexibility for forecasting, I think it might be a good approach to provide a forecast. Also managers will be able to provide weights on different years according to their experience (ignoring transient data peaks). I hope I am following a good approach. Any suggestions from you would be great help.

                 

                Again thanks a lot for your help. I will now work towards finalizing my dashboard model.

                • 5. Re: Predictive Analytical Modelling
                  Dimitri.B

                  Sounds good, just beware that the larger the 'fudge factor' (applying arbitrary weights to historic values), the less of a forecast it becomes.

                  You can use the same technique as for 2012 to add weights to past years, but maybe limit the range of the weights?