How to calculate working hours spent for a CASE on a activity

For Ex Time spent on Deliver block activity. I am using below formula

DATEDIFF ( HH, PU_FIRST(VBAP, “_CEL_O2C_ACTIVITIES”.“EVENTTIME”, “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” IN (‘Set Delivery Block’, ‘Set Default Delivery Block’)),
PU_FIRST(VBAP, “_CEL_O2C_ACTIVITIES”.“EVENTTIME”, “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” IN (‘Remove Delivery Block’)) )
But this formula will consider weekend and holiday hours as well.
Please let me know how to get the working hours only .

I have tried below formula as well after looking at


as i dont have any calender table to use.But Below formula is also not working.

CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Set Delivery Block’] TO LAST_OCCURRENCE[‘Remove Delivery Block’], REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES".“EVENTTIME”, MONDAY 08:00-17:00 TUESDAY 08:00-17:00 WEDNESDAY 08:00-17:00 THURSDAY 08:00-17:00 FRIDAY 08:00-17:00 ),
REMAP_VALUES("_CEL_O2C_ACTIVITIES".“ACTIVITY_EN”, [‘Set Default Delivery Block’, ‘Set Delivery Block’])
)

Hello @minakshi2112,

thank you for the great question!

I have attached a .txt file for you with a PQL formula that you could try for this case.

Tell me more about the Holidays and how you plan on including them.

Let me know if this does not work for you!

Your,

Celonis Team

WorkingHours.txt (372 Bytes)

1 Like