Direct and reverse measure calculation

[PaulKass]

Hi everybody,

Imagine a problem. My company places orders and gets shipments, the orders and shipments are in one-to-one relationship but the orders are not always perfectly fulfilled and sometimes we get less than ordered. My task is to calculate the service level, i.e. Sum(ShipmentQty) / Sum (OrderQty).

Let's say we ordered 50 pcs of Part A and 150 pcs of Part B but got only 50 pcs of Part A. Below is the script to show this situation:

Orders:
Load
*,
OrderNo&'|'&PartNo   as   key
Inline [
  OrderNo, PartNo, OrderQty
  O1, A, 50
  O1, B, 150
];

Shipments:
Load * Inline [
  ShipmentNo, key, ShipmentQty
  S1, '1|A', 50
];

If I use Sum(ShipmentQty) / Sum(OrderQty) as Service Level I have no problem if I select OrderNo = O1. The result is 0.25 as expected.

But if I want to make a "reverse calculation" selecting ShipmentNo = S1 I get a wrong result equal to 1.

How can I correctly define a measure to calculate the Service Level if I want to start from the shipment?

[Chanty4u]

Try this 

vTotalOrderQty = Sum({<ShipmentNo = {'S1'}>} OrderQty)

ServiceLevel = Sum(ShipmentQty) / $(vTotalOrderQty)

[PaulKass]

Unfortunately the result is as from Sum(ShipmentQty) / Sum(OrderQty).

Instead I have tried to use 

Service Level = Sum(ShipmentQty) / Sum(Total{<OrderNo={'O1'}, ShipmentNo=>}OrderQty)

It works but I have plenty of orders and do not know how to assign the correct OrderNo values

[Kushal_Chawda · Accepted solution]

@PaulKass  try below

Sum(total <OrderNo>{<ShipmentNo=>}OrderQty)

[PaulKass]

Thank you!

It works but a very strange way... I will deep into it )