October 27, 2014 at 11:38 am
Hi everyone,
First post in here about this so here goes.
We use MS Dynamics Nav as our ERP and I'm having difficulty stitching together forecasts and actuals to give a full year position.
Item_ID = Item_1 is sold in units of 10. For each Item_1 outer we sell, this contains 10 inners. We store both elements of data per transaction line
Item_ID is a surrogate key linked to the item hierarchy.
Customer_ID = Customer_1 who is one of our customers to whom we sell Item_1.
We have iterations of forecast which we revise monthly
Forecast 1 is FC1
Forecast 2 is FC2
etc..
Each forecast is an estimate at that point in time, and always looks forward.
i.e.
FC1 is the forecast done for the period of June through October
FC2 is the forecast done at the end of June, looking at July through October
etc...
Forecasts are stored per item, per customer, per date in a table called Item Budget entry.
If we planned to sell customer 1, Item 1
1 outer in Jun
2 outers in Jul
3 outers in Aug
4 outers in Sep
5 outers in Oct
the data might would look like:
FC1| Item_1| Customer_1| '2014-06-01'|1|10
FC1| Item_1| Customer_1| '2014-07-01'|2|20
FC1| Item_1| Customer_1| '2014-08-01'|3|30
FC1| Item_1| Customer_1| '2014-09-01'|4|40
FC1| Item_1| Customer_1| '2014-10-01'|5|50
Then if we revise the forecast in the iteration FC2,
3 outers in Jul
4 outers in Aug
5 outers in Sep
6 outers in Oct
these get appended to the table so it now looks like:
FC1| Item_1| Customer_1| '2014-06-01'|1|10
FC1| Item_1| Customer_1| '2014-07-01'|2|20
FC1| Item_1| Customer_1| '2014-08-01'|3|30
FC1| Item_1| Customer_1| '2014-09-01'|4|40
FC1| Item_1| Customer_1| '2014-10-01'|5|50
FC2| Item_1| Customer_1| '2014-07-01'|3|30
FC2| Item_1| Customer_1| '2014-08-01'|4|40
FC2| Item_1| Customer_1| '2014-09-01'|5|50
FC2| Item_1| Customer_1| '2014-10-01'|6|60
Sales are derived using a query which takes the "Union All" of our sales Invoice lines and Sales Credit memo lines tables. In the cube we are able to see this data which in SQL looks like:
ITEM_ID|CUSTOMER_ID|Date|Volume in measure 1|Volume in measure 2
Item_1|Customer_1|'2014-01-01'|1|10
Item_1|Customer_1|'2014-02-01'|2|20
Item_1|Customer_1|'2014-03-01'|-1|-10
Item_1|Customer_1|'2014-04-01'|4|40
Item_1|Customer_1|'2014-05-01'|4|40
Item_1|Customer_1|'2014-06-01'|3|30
Item_1|Customer_1|'2014-07-01'|6|60
We have relationships via surrogate keys for the Items and customers. Dates are linked to a date table for grouping purposes. As individual measure groups these work brilliantly, however, I've now been asked to "stitch" together the Sales and the forecasts based on the logic that you take the sales up to the start of the forecast (which is defined as a field we store in the DB imaginatively called "Forecast Start Date")
that is:
At forecast iteration FC1 the full year (by month) is:
Sum of sales before the forecast starts
Month: Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct
Sales: 1 | 2 | 3 | 4 | 3 | 6 |
FC1: 0 | 0 | 0 | 0 | 0 | 1 | 2 | 3 | 4 | 5
FC2: 0 | 0 | 0 | 0 | 0 | 0 | 3 | 4 | 5 | 6 |
cumulated to read:
Month: Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct |
FC1: 1 | 2 | 3 | 4 | 3 | 1 | 2 | 3 | 4 | 5 |
FC2: 1 | 2 | 3 | 4 | 3 | 6 | 3 | 4 | 5 | 6 |
Then there is a requirement to summate "that which is in the past" - so, using another member from the "Date" hierarchy called "YMD" we wish to calculate the running total per FC1 and FC2 to that point in time. ie if I set the date to be 1st August, I would expect YTD to be sum Jan through 31st July , YTG to be 1st August through end of year and The full year to be YTD + YTG
For ITem 1, Customer 1 in "outers"
Month: Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct | YTD| YTG | Full Year |
FC1: 1 | 2 | 3 | 4 | 3 | 1 | 2 | 3 | 4 | 5 | 16 | 12 | 28 |
FC2: 1 | 2 | 3 | 4 | 3 | 6 | 3 | 4 | 5 | 6 | 22 | 15 | 37 |
or in "inners"
Month: Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct | YTD| YTG | Full Year |
FC1: 10 | 20| 30| 40| 30 | 10 | 20| 30| 40 | 50 | 160| 120| 280 |
FC2: 10 | 20| 30| 40| 30 | 60 | 30| 40| 50 | 60 | 220| 150| 370 |
Can anyone give me any pointers on how to achieve this? I've no idea where to even start!
Many thanks
Tattysnuc
October 29, 2014 at 7:32 am
So just to clarify, the stitching together isn't to include the actuals, it's more just FC1 +FC2 + ... + FCn? Versus the approach of Actuals_To_date + FC month values not yet come to pass (ie. it's now October, we'd have actuals (Jul-Oct) + Forecast(Nov-Dec))
Steve.
October 29, 2014 at 8:04 am
Not quite.
The history is contained in the Actuals which is one measure group. there is only one version of sales.
the forecast is revised monthly, so there are multiple forecasts, each one starting a month later than the last, but all contained in the same underlying table.
October 29, 2014 at 8:08 am
unless I'm misunderstanding your answer.
So, in September when we input the forecast (FC1) for the rest of the year, we have:
Sales Jan through Sept, then a forecast (called FC1) starting in Oct.
In October we have the same Sales, but now including October so Sales are for Jan through Oct. The next Forecast (FC2) will start in Nov
etc etc..
October 29, 2014 at 9:07 am
Confusing much? 😉
I guess another way to ask this is, are you looking to have a measure that shows whatever actuals exist plus whatever forecast exists (ie for whatever periods no actuals exist yet), which would give you a blended result of actuals + forecast of remaining periods.
Steve.
October 29, 2014 at 10:44 am
yes mate 🙂
My problem is that sales exist AFTER the historical forecasts begins so we need to ALWAYS show Forecast, and only include the sales up to the start of date of that particular forecast.
October 29, 2014 at 11:29 am
So, will there be a dimensional attribute that the user can select to inform us of the 'selected' period (eg month)? Or alternatively, will they need ot select a forecast from a list of FC's in a dimension?
Assuming we can get one of the above, am guessing you'll likely want to use a calculated member (measure) and SCOPE. Something like
create/declare calc measure, set value = 0
use scope(limiting to selected FC member perhaps)
calc_measure = (months of actuals, measures.actuals) + (months of forecast, measures.forecast)
close scope
Steve.
October 30, 2014 at 3:54 am
stevefromOZ (10/29/2014)
So, will there be a dimensional attribute that the user can select to inform us of the 'selected' period (eg month)? Or alternatively, will they need ot select a forecast from a list of FC's in a dimension?Assuming we can get one of the above, am guessing you'll likely want to use a calculated member (measure) and SCOPE. Something like
create/declare calc measure, set value = 0
use scope(limiting to selected FC member perhaps)
calc_measure = (months of actuals, measures.actuals) + (months of forecast, measures.forecast)
close scope
that sounds like the solution, although I haven't got a clue with MDX script. I'm going to have a read of the "stairway" when I get home tonight and see where that takes me...
The cut off date for the start of the forecast is held in a field in the dsv called Forecast Begin date, and is defined per forecast iteration.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply