Attached is the sample data.
I am loading 4 primary tables:
- Transactions - Has all the transactional data for work orders
- Costs - Has all the labor costs for parts
- Labor Hours - Has the labor hours reported for a work order
- Customer - Has the customer name linked to the part number
Transactions.Part_ID -> Costs.Part_ID
Transactions.Workorder_ID -> Labor Hours.WorkOrder_ID
Transactions.Part_ID -> Customer.Part_ID
In order to calculate my standard labor minutes for a part, I have to perform math against the cost. We're only trying to capture the productivity at the top level, so if the part has a sub-assembly I need to use the labor cost less the subassembly. If the part does not have a sub assembly I use just its labor cost.
The formula I am using looks like this for that:
This formula should give me the calculated labor std minute for a part based on its labor cost (the 9.46 is a standard labor dollar amount that I made up for this sample)
Then in order to calculate the actual labor hours spend on a part, I need to summarize the transactions and labor hours for the work order/part and then multiply them by each other to get the actual reported time.
Actual labor minutes: Sum(Transactions.Trans_Qty) * Sum(Labor Hours.Process_Hrs)*60
Difference back in hours: (If(isnull(laborcost_lesssubassembly),laborcost,laborcost_lesssubassembly))/$9.46)*60) - (Sum(Transactions.Trans_Qty) * Sum(Labor Hours.Process_Hrs)*60) / 60
Now - I need this data (the difference) to be bar charted by Qtr, Month, Week, Customer, Part, Customer/Part.
Does this help you?
SampleData.xlsx 10.4 K
to get Month, Week, quater, year
year(Trans_Date) as Trans_Year,
Month(Trans_Date) as Trans_Month,
ceil(num(Month(Trans_Date))/3) as Trans_quarter,
week(Trans_Date) as Trans_Week,
(ooxml, embedded labels, table is Transactions);