2 Replies Latest reply on Jan 31, 2016 11:44 AM by Andy Holt

    Comparing forecasts and actuals - connecting points when plotting a series

    Andy Holt

      Hi there

       

      I am hoping someone is helping to help with this. As you will see in the attached workbook (showing all of my working) I have tried a number of different approaches to the problem but nothing seems to work.

      I possibly think I have got close to a solution (without getting 100% of the way there) by 'chance' but don't quite understand what is happening enough to take it that final step. "Enough rope to hang myself..." perhaps.

       

      Context

      Here is the context so you can understand what I am trying to achieve:

      • We have two forecasters within the company, FC1 & FC2
      • Every month both forecasters forecast sales for the coming 18 months
      • I want to compare actual sales for a month to the forecast made for that month x months ago (where x is controlled by a parameter)
      • To compare this I want to build a control chart plotting two series (FC1 variation from actuals, FC2 variation from actuals) building it up month by month.

       

      What I've done so far

      Let me talk you through my workbook so you can understand my thinking (and perhaps clarify where I've gone wrong!):

      • [data]
        • Simply shows the raw data. For three months-worth of actuals I have the relevant 18 months of forecast for each of the three months, for both FC1 andf FC2, with the date each forecast was made
      • [data with lag]
        • Using a parameter I am able to compare sales to the forecast made x months ago
        • I have attempted two solutions here as I am not sure which is best, FC1-x & FC1-x (v2)
        • I am not sure it matters but both achieve what I need them to for the next step
      • [data with lag (filter options)]
        • I only need to consider the last value for each forecast month but there are two possible ways of getting that
          • Using last()
          • Using a test to ensure the forecast date equals the actuals date
        • Both seem to work as you will see in [key data (last)] and [key data (date)]
      • [key data (last)] and [key data (date)]
        • These use the two filters I built to respectively filter down to the key info - actual sales and the forecast made x months ago
      • [key data (last) with var] and [key data (date) with var]
        • I know introduce the variation calculation I want to use
        • There are two versions, one each for the two lag options
      • [key data (last) just var] and [key data (date) just var]
        • I now strip out the sales data to just leave the variation calculations to ensure they both work. Still all good.
        • This is what I want to plot
      • [charts (last)] and [charts (date)]
      • [final (ish)]
        • Ultimately what I want is along these lines
        • I have achieved it using dual axes which allows me to label the points individually, but I still cannot get them to join

       

      Questions:

      1. If my final solution looks like the [final (ish)] sheet, how do I colour the points that exceed control (reference) lines different to the rest?

      2. Is someone please explain which lag option and filtering option are best for me and why?

      3. Phase two is potentially looking at cumulative forecasts and sales for the past x months (rather than discrete months) - any ideas on that little puzzler?

       

      Basically, is my data structure wrong? Should I be using a table with one measure and a dimension to describe the measure?
      I am hoping not, but I do wonder if that is the answer to my problems.

       

      Many thanks indeed

       

      Andy

        • 1. Re: Comparing forecasts and actuals - connecting points when plotting a series
          Adam Crahen

          Hi Andy-

           

          Hopefully this is some help.  I didn't answer everything.

           

          If you move Month(Month Forecast Generated) to Path in your marks card, your points will connect.

          2016-01-28_23-43-11.png

           

          1) I wrote two calculated fields to the color the points outside of your -3-3% reference band

           

          FC1 Color

          IF [FC1-Act variation (v2)] > -.03 AND [FC1-Act variation (v2)] <.03

              THEN "Gray" Else "Red" END

           

          Same thing for FC2 Color, but different measure referenced.

          I also labeled your points FC1/FC2 at this point.  Or could use a different color scheme for one.

          2016-01-28_23-45-32.png

           

          I think you might try and pivot your data in Tableau.  In your edit data source window, select the FC1/FC2 columns and click the dropdown to pivot.  Then you get two new fields: pivot field names (i.e., FC1, FC2) and pivot field values (#).  This will probably make things easier in your next go around.

           

          9.1.3 workbook attached.

          3 of 3 people found this helpful
          • 2. Re: Comparing forecasts and actuals - connecting points when plotting a series
            Andy Holt

            Awesome. Thanks so much, Adam.
            I thought I had tried every date field with discrete/continuous combination on the path mark but clearly not. I guess I missed the key one

             

            Thanks again.