First of all I just want to explain what I have done so far: I took all the Purchase Orders we placed to our factory for goods. I then took all the Goods Receipts that we created when we received the goods from the factory. I then matched all the Purchase Orders with the Goods Receipts in order to see if we have received all the goods on time and in full. Thus the measurement is known as On time in full (OTIF).
The way OTIF works is as follow: You take all the items on a specific Purchase Order & match it to the appropriate Goods Receipt. If the specific Item was received on time and in full it gets a 100%. If it hasn't been received on time & in full it gets a 0%. Now, if all the items on a specific Purchase order was received on time & in full, the Purchase orders gets a rating of 100%. If only one of the items was not received on time & in full the Purchase Order gets a rating of 0%. Then you count the total number of purchase orders and you count the number of purchase orders that has a 100% rating. You then get for expample: 25/75 = 33% OTIF result.
The problem I have is how to tell Qlikview to check all the items first and then give a total result for that specific purchase order (100% or 0%)
I have attached a spreadsheet that will show you my problem. You will see that I got a 25% OTIF result of all the line items. The theory behind this is correct, but you will see that none of the Purchase orders was received in full. Thus the OTIF result must be 0%.
I would appreciate this alot if someone can have a look at this and recommend a solution to my problem.
Thank you for the reply. This is definitely in the right direction but not completely what I am looking for.
You will see that in the attached model I have updated some of the lines. You will now see that all of the items on one of the purchase orders (450006687) has been received on time & in full. The problem now is that the OTIF Total should show 20%. The 20% is calculated by counting the number of Purchase orders and also counting the number of purchase orders that has been fully receipted. It is then 1/5 = 20% OTIF.
