Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
dapostolopoylos
Contributor III

Calculation of OTIF KPIs for Purchase Orders

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?


Father/Husband/BI Developer
7 Replies
dapostolopoylos
Contributor III

Re: Calculation of OTIF KPIs for Purchase Orders

Up

Father/Husband/BI Developer
sergio0592
Valued Contributor II

Re: Calculation of OTIF KPIs for Purchase Orders

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
Contributor III

Re: Calculation of OTIF KPIs for Purchase Orders

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.

Father/Husband/BI Developer
sergio0592
Valued Contributor II

Re: Calculation of OTIF KPIs for Purchase Orders

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
Contributor III

Re: Calculation of OTIF KPIs for Purchase Orders

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?

Father/Husband/BI Developer
sergio0592
Valued Contributor II

Re: Calculation of OTIF KPIs for Purchase Orders

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
Contributor III

Re: Calculation of OTIF KPIs for Purchase Orders

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.

Father/Husband/BI Developer