Skip to main content

Hi,

I am currently working on getting the Manual Delivery Rate?

What do i need to modify on this code that is currently getting the On-Time Delivery Rate?

100.0*AVG(

CASE

WHEN ISNULL(<%=DUE_DATE%>) = 1 THEN NULL No Confirmation

WHEN ISNULL(
PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>')
) = 1 AND DAYS_BETWEEN(<%=DUE_DATE%>,TODAY()) <= 0 THEN NULL --'No GI and Future Due Date'

WHEN ISNULL(
PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>')
) = 1 AND DAYS_BETWEEN(<%=DUE_DATE%>,TODAY()) > 0 AND DAYS_BETWEEN(<%=DUE_DATE%>,TODAY()) <= <%=TOO_LATE_BUFFER%> - COALESCE(<%=ROUTE%>*1.0,0.0) THEN NULL -- 'No GI and Passed Due Date, but still within tolerance'

WHEN ISNULL(
PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>')
) = 1 AND DAYS_BETWEEN(<%=DUE_DATE%>,TODAY()) > <%=TOO_LATE_BUFFER%> - COALESCE(<%=ROUTE%>*1.0,0.0) THEN 0.0 -- 'No GI and Passed Due Date and out of tolerance'

WHEN ROUND(DAYS_BETWEEN(
PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>')
,<%=DUE_DATE%>)) > COALESCE(<%=ROUTE%>*1.0,0.0) + <%=TOO_EARLY_BUFFER%>
THEN 0.0 --'Too Early'

WHEN ROUND(DAYS_BETWEEN(
PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>')
,<%=DUE_DATE%>)) >= COALESCE(<%=ROUTE%>*1.0,0.0)
AND ROUND(DAYS_BETWEEN(
PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>')
,<%=DUE_DATE%>)) <= COALESCE(<%=ROUTE%>*1.0,0.0) + <%=TOO_EARLY_BUFFER%>
THEN 1.0 --'On Time' - earlier than due date, but within too early buffer

WHEN ROUND(DAYS_BETWEEN(
PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>')
,<%=DUE_DATE%>)) < COALESCE(<%=ROUTE%>*1.0,0.0)
AND ROUND(DAYS_BETWEEN(
PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>')
,<%=DUE_DATE%>)) >= COALESCE(<%=ROUTE%>*1.0,0.0) - <%=TOO_LATE_BUFFER%>
THEN 1.0 --'On Time' - later than due date, but within too late buffer

ELSE 0.0 --'Too Late'
END

)

Regards,

Kenneth

Hello Kenneth,

In order to calculate the Manual Delivery Rate for activity Record Goods Issue, you need to know your automatic user types in the first place. Generally, B user types are automated but there might be more automated user types. You can use the following general framework to calculate the manual rate. If there are more automated user types, simply add them in the brackets accordingly.

100.0*AVG(
CASE WHEN ISNULL("_CEL_O2C_ACTIVITIES"."USER_TYPE") = 1
THEN NULL --exclude empty user types
WHEN "_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"!='<%=record_goods_issue_activity%>'
THEN NULL --only include GI activity
WHEN "_CEL_O2C_ACTIVITIES"."USER_TYPE" NOT IN ('B') AND "_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>'
THEN 1.0
ELSE 0.0
END)

If you have an further questions, please do not hesitate to ask.

Best,

Viana

Reply


Terms & Conditions

ANY QUESTION?

Talk to an expert
FAQs
Contact us

MORE CELONIS

Academy arrow_outward
Documentation arrow_outward
Support Center arrow_outward
Partner Portal arrow_outward
Marketplace arrow_outward

CHECK US IN

LinkedIn
YouTube
Instagram
Facebook
Imprint
Disclaimer
Privacy Notice
Cookie Notice
Terms and Conditions
Celonis © 2024