Challenging Forecast Wape - Rolling 12 Month Sum Of Orders And Forecast, Access 2007

[nguyeda]

Hello,

My hair is literally turning white trying to figure this out. I'm not sure what is the best way to do this so I hope you experts can help me. I'm not at work so I don't have the examples with me. It's not that complicated.

Basically I have a table called "EDW" which is an SAP extraction. It has a list of items, each item has a date for how many historical demands there were and how much was the absolute error (abs(Historical Demand - Total Forecast) for that month.

Example:

Item - Month - Historical Demand - Absolute Error - Group
X 03/01/2012 100 50 A
X 04/01/2012 100 50 A
X 05/01/2012 100 50 A
X 06/01/2012 100 50 A
X 07/01/2012 100 50 A
X 08/01/2012 100 50 A
X 09/01/2012 100 50 A
X 10/01/2012 100 50 A
X 11/01/2012 100 50 A
X 12/01/2012 100 50 A
X 01/01/2013 100 50 A
X 02/01/2013 100 50 A
X 03/01/2013 100 50 A
X 04/01/2013 100 50 A
X2 03/01/2012 100 50 B
X2 04/01/2012 100 50 B
X2 05/01/2012 100 50 B
X2 06/01/2012 100 50 B
X2 07/01/2012 100 50 B
X2 08/01/2012 100 50 B
X2 09/01/2012 100 50 B
X2 10/01/2012 100 50 B
X2 11/01/2012 100 50 B
X2 12/01/2012 100 50 B
X2 01/01/2013 100 50 B
X2 02/01/2013 100 50 B
X2 03/01/2013 100 50 B
X2 04/01/2013 100 50 B


There are about 9,000 items and 24 months of data and probably 12 groups, then those groups have a group, and those groups actually have a group.

Think of it as Toyota Corporate having 2 brands of Cars... Toyota and Lexus thats your highest group, then you might have a set of models like (SUV, Sedan, Sports Car), then under that group you have the specific models, then under that group you have the parts of the car. Basically we want to know the forecast accuracy at the specific models, then again rolled up the set of models, then rolled up to either Lexus or Toyota then all the way up to Toyota Corporate.


Now the formula is as such:

MAPE: Sum (Last 12 Months of Error / Sum of last 12 Months of Historical Demand)*100

then get your Group Weight.... So, it'll be the Sum of Demands for each item X / Sum of Demands over the Group

Then its MAPE * Group Weight

So the main challenge I'm facing before I get to the stacking of the groups is getting the sum of the rolling 12 months.

It'd mean the world to me if you'd help! I'd buy you guys so much beer.

Thank you!!!

By the way, right now I'm trying to tackle it by creating TONS of sub queries for each group and each section of the formula. It's insanity.

 

[plog]

You've given me too much overview and not enough detail. Its always easier when you explain what you want with specific data examples. I have a vague idea of the system, but without a low level example to go by I can't help you specifically.

Post some sample data, include table and field names and then post what the results should be based on that sample data. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
ABC, 4/2/2008, 75
DEF, 5/1/2010, 112
GHI, 4/26/2007, 88

 

[nguyeda]

Sorry, does this help?

EDW
Item, Cal year / month, Historical Demand, Abs Error, Group
X, 03/01/2012, 100, 50, A
X, 04/01/2012, 100, 50, A
X, 05/01/2012, 100, 50, A
X, 06/01/2012, 100, 50, A
X, 07/01/2012, 100, 50, A
X, 08/01/2012, 100, 50, A
X, 09/01/2012, 100, 50, A
X, 10/01/2012, 100, 50, A
X, 11/01/2012, 100, 50, A
X, 12/01/2012, 100, 50, A
X, 01/01/2013, 100, 50, A
X, 02/01/2013, 100, 50, A
X, 03/01/2013, 100, 50, A
X, 04/01/2013, 100, 50, A
X2, 03/01/2012, 100, 50, B
X2, 04/01/2012, 100, 50, B
X2, 05/01/2012, 100, 50, B
X2, 06/01/2012, 100, 50, B
X2, 07/01/2012, 100, 50, B
X2, 08/01/2012, 100, 50, B
X2, 09/01/2012, 100, 50, B
X2, 10/01/2012, 100, 50, B
X2, 11/01/2012, 100, 50, B
X2, 12/01/2012, 100, 50, B
X2, 01/01/2013, 100, 50, B
X2, 02/01/2013, 100, 50, B
X2, 03/01/2013, 100, 50, B
X2, 04/01/2013, 100, 50, B

 

[plog]

Not completely. Now based on the data you've posted, what do you want the results to be?

 

[nguyeda]

For every month you'd do

50/100

Then you'd sum up the last 12 months of that equation (Error/Historical Demand)

SO you'd get 6. Then Divide that by 12 and you'd get .5 (used below)

Then the 2nd part you'd get the sum of demands for the past 12 months for the group which is 1200. Then the sum of demands for the item for the past 12 months which is also 1200.

So the weight 1200/1200 is 1.

You'd multiply the weight of 1 * .5

So the answer is .5 for Group A but Group B in this example would be the same.

The example is WAY to simple the math looks redundent because there is only 1 item but each group could have a 1,000 to 3,000 items. It has to be a rolling 12 months.

So you'd do 03/01/2012-02/01/2013, 04/01/2012-03/01/2013, then 05/01/2012-04/01/2013

 

[plog]

I don't want words, I want data. Post some sample starting data and then post what you want as a result based on that sample data.