Dynamic On Time Delivery

[s_gorgo]

Hello,

I'm going crazy 😀

I need to calculate the on time delivery KPI of the plant. Simple... on paper.

Sales Order	Code	Requested Date	Delivery Date	Req Wk	Delay
123	Object123	20/07/2020	20/07/2020	30	FALSE
124	Object123	20/07/2020	21/07/2020	30	TRUE
125	ObjectABC	17/07/2020	20/07/2020	29	TRUE
126	ObjectABC	17/07/2020	15/07/2020	29	FALSE
127	Object123	10/07/2020	-	28	TRUE
128	Object123	10/07/2020	10/07/2020	28	FALSE
129	ObjectABC	10/07/2020	15/07/2020	28	TRUE

Each weeks I have the number of object that we have to deliver.

Incorrect KPI:

Req Wk	# pcs requested	# pcs on delay
28	3	2
29	2	1
30	2	1

Why incorrect? Because I don't consider the delay of the previous week in the workload.

Correct KPI:

Req Wk	# pcs requested	# pcs on delay
28	3	2
29	4 (2 wk29 + 2 delay wk28)	2 (1 wk29 + 1 wk38)
30	4	2 (1 wk30 + 1 wk28)

While the "incorrect" KPI is easy for me to calculate, I don't know where to start to calculate the most appropriate for me.

Has anyone had a similar experiences? Can you help me please?

Thank you!

[Kushal_Chawda]

for # pcs on delay , Week 29,  1 delay is for 29 + how 1 is for week 28? Is it not 2 for week 28?

[s_gorgo]

Hello Kush,

thanks for your reply. 😉

At the end of the week 29 we have 2 delays in this example:

• S.O. 125 (requested date 17/7, not yet delivered)
• S.O. 127 (requested date 10/7, not yet delivered) --> delay in wk28, delay in wk29

[s_gorgo]

Up! 😊

[Kushal_Chawda]

try below

T1:
LOAD
    "Sales Order",
    Code,
    "Requested Date",
     if(len(trim("Delivery Date"))=0,1,0) as Not_Delivered_Flag1,
    Week("Requested Date") as Req_Week,
    "Delivery Date",
    Delay
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);

T2:
Load Req_Week,
     Count("Sales Order") as Order_count
Resident T1
Group by Req_Week;

Left Join (T2)
Load  Req_Week,
     sum(Not_Delivered_Flag1) as Not_Delivered_cnt1
Resident T1
Group by Req_Week;

Drop Field Not_Delivered_Flag1;

Left Join (T2)
Load Req_Week,
     Count("Sales Order") as Delay_count
Resident T1
where lower(trim(Delay))='true'
Group by Req_Week;

T3:
Load Req_Week,
     Order_count,
     Not_Delivered_cnt1,
     Delay_count,
     if(Not_Delivered_cnt1>0,Not_Delivered_cnt1,rangesum(Peek(Not_Delivered_cnt),Not_Delivered_cnt1)) as Not_Delivered_cnt,
     if(RowNo()=1,Delay_count,rangesum(Delay_count,Peek(Not_Delivered_cnt))) as PC_Delay,
     if(RowNo()=1,Order_count, rangesum(Order_count,Peek(PC_Delay))) as PC_Requested
Resident T2
Order by Req_Week;

Drop Table T2;

Drop Fields Order_count,Not_Delivered_cnt1,Not_Delivered_cnt,Delay_count;