August 28, 2007 at 10:32 am
I am working on a project at a manufacturing client. The measure I'm having trouble with is LeadTime (the number of days from manufacturing schedule to completion). It is used in a calculation to determine over/under inventory levels.
I need to use the actual LeadTime for the calc at the SKU level and the average LeadTime at all of the aggregate levels.
SKU is the lowest level of data...the individual product being manufactured and sold. The LeadTime does not need to be calculated it is already determined for each product. Here is a table that I hope helps:
LeadTime | ||
Product | Actual | Average |
1001 | 3 | 4 |
1002 | 4 | 4 |
1003 | 5 | 4 |
Sub-total Sum | 12 | |
Sub-total Avg | 4 | |
Three product ID’s (1001, 1002, 1003) with actual LeadTime for each product in days (3, 4, 5 – respectively).
The LeadTime measure is used with other measures (On-hand, Daily demand, Safety stock, etc.) in an inventory over/under calculation. But, LeadTime is the only non-additive measure. Because of that the calculation is correct at the Product ID level (3, 4, 5), but wrong at the aggregate level (12). If I set the measure to Average LeadTime the Product level is wrong (4, 4, 4), but the aggregate is correct (4). I’m looking for a solution that gives me the correct calculation at all levels
August 28, 2007 at 10:51 am
I'm not sure if there's a SQL-related question, or if it's a business rule you're grappling with.
That being said - if a particular aggregation isn't meaningful, then don't use it. Perhaps instead of being additive, the meaningful aggregation is the average, or the maximum (on an order by order basis for example, the "handling time" or "lead time" would likely be the time it takes to put an order together, which would be the MAX of all of the lead times of the items being ordered). On the other hand - if it were an assembly line - then lead time becomes a much less obvious concept depending on how many are being ordered (just because it takes 4 days to produce 1 tells you nothing per se about how long it takes to produce 10,000, since there's nothing describing how many at a time you might be able to make, etc...).
Just be careful of "fake twins" (two distinct concepts being called the same thing by the users, but meaning different things in different contexts). From your discussion on the matter - sounds to me like you have (at least) two different animals, each being used under different circumstances and each valid only in specific circumstances.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply