Comparing forecasts and actuals  connecting points when plotting a series
Andy Holt Jan 28, 2016 7:41 PMHi 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 monthsworth 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, FC1x & FC1x (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)]
 I only need to consider the last value for each forecast month but there are two possible ways of getting that
 [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)]
 Plotting as four separate charts I can get the data I want to show
 However, the plots are just dot plots and I cannot figure out how to get the lines to join
 I figured it was something along these lines: Connecting points on a line chart using discrete and aggregate data , but I cannot get it to work
 [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

forecasting  Lookup.twbx 151.3 KB