I have a request to create a report that monitors the OTIF (On Time - In Full) KPI for the Purchse Orders of the company i am working for. Each purchase order may have many purchase order items and is accompanied by the following dates:
For every month we must report which purchase orders were delivered, in full or not, on time or not...
If a purchase order isn't completely delivered on time and there is still quantity to be delivered then this purchase order/purchase order item must show up and in the next months report.
Any suggestions on how to implement this request?
How you get these following informations " were delivered, in full or not, on time or not..."??
I suppose that you have to compare fields? Requested Delevery Date VS Requested Delivery Date @ Customs?
Correct, i compare date fields.
A purchase order item is considered to be delivered when the Goods Received Date@ Customs is not null.
Accordingly, in order to calculate if the delivery is On Time i compare Goods Received Date@ Customs and the Requested Delivery Date @ Customs.
And if you create two flags FL_delivery_Y_N and FL_delivery_on_time with :
if (len(Goods_Received_Date_@_Customs)>0,1,0) as FL_delivery_Y_N,
if(Requested_Delevery_Date<Goods_Received_Date_@_Customs,1,0) as FL_delivery_on_time
Order_Id, Document_Date,Requested_Delevery_Date, Requested_Delivery_Date_@_Customs, Goods_Received_Date, Goods_Received_Date_@_Customs
456UJ,02/06/2018, 05/06/2018, 05/06/2018, 05/06/2018, 05/06/2018
457UJ,05/06/2018, 08/06/2018, 12/06/2018, 12/06/2018, 12/06/2018];
Hi, Jean-Baptiste and thank you very much for your help.
This idea of yours could work and i will give it a try.
The tricky point about this request of mine isn't just the calculation of the KPIs but also the ability of the user by selecting the Report Month (Jun 2018 for example) is to get a list of all the purchase orders that are delivered/should be delivered/are pending in the selected month.
The calculation of the KPIs should be done on the chart level for my underastanding.
Any thoughts on that?
Seems to be a good idea Jean-Baptiste, i am already working on your suggestion.
The only defect i see on your solution is that i cannot keep track of the non delivered orders as long as we have linked the Requested Date @ Customs with the Report Month.