Calculation of OTIF KPIs for Purchase Orders

[dapostolopoylos]

Hello, experts.

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:

1. Document Date
2. Requested Delevery Date
3. Requested Delivery Date @ Customs
4. Goods Received Date
5. Goods Received Date  @ Customs

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?

[dapostolopoylos]

Up

[sergio0592]

Hi,

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?

[dapostolopoylos]

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.

[sergio0592]

And if you create two flags  FL_delivery_Y_N and FL_delivery_on_time with :

LOAD *,
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
INLINE [
   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];

[dapostolopoylos]

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?

[sergio0592]

Hi, so you can use a pivot table in order to get the list.

See the attached .qvw. Is it what you're trying to achieve?

[dapostolopoylos]

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.