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 it to use the actual LeadTime for the calc at the SKU level and the average LeadTime at all of the aggregate levels.
Thanks for the help,
Dave
Hello! I am not sure about what you mean with SKU level but if you have two dates in the fact table, ManufacturingScheduleDate and ManufacturingCompletionDate, you can make a named calculation in the data source view(Analysis Services 2005) or a calculation in the ETL-process with SSIS.
Use the TSQL function DATEDIFF() for that and the difference between the MScheduleDate and the MCompletionDate.
I am not sure about your problem with the averages and what you are doing averages of?
You should be able to solve this with the MDX AVG() function.
HTH
Thomas Ivarsson
|||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
When I set the measure LeadTime to Sum the calculation that uses LeadTime is correct at the product level, but wrong at the aggregate level.If I set the measure to Average then the calc is wrong at the product and right at the aggregate level.
I need to show the cube data with LeadTime as actual at the product level and average at the aggregate level.
Dave
|||
Hello! Can you also indicate the expected values in the table for Actual and Average Lead time?
From what I have seen with other clients is that you have a manufacturing order id for each production of a product.
It is not a part of your table but can it be a part of the problem?
Regards
Thomas Ivarsson
|||Here is a little description of the table from my last post.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 at 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
This project is only dealing with inventory levels.Orders are not a part of the calculation except as a part of the Daily Demand figure.
