Skip to main content
Hi, Celonis Community! I am calculating the time between events for employee as: demension1 SOURCE (20_DOCS_EVENT. EVENT, CASE WHEN 20_DOCS_EVENT. EVENT = event1 OR 20_DOCS_EVENT. EVENT = event2 OR 20_DOCS_EVENT. EVENT = event3 OR 20_DOCS_EVENT. EVENT = event4receive OR 20_DOCS_EVENT. EVENT = event5approve THEN 20_DOCS_EVENT. EVENT ELSE NULL END) demension2 TARGET (20_DOCS_EVENT. EVENT) and filter FILTER SOURCE (20_DOCS_EVENT. EVENT, CASE WHEN 20_DOCS_EVENT. EVENT = event4receive OR 20_DOCS_EVENT. EVENT = event5approve THEN 20_DOCS_EVENT. EVENT ELSE NULL END) = event4receive; FILTER TARGET (20_DOCS_EVENT. EVENT) = event5approve; demension3 20_DOCS_CASES.EMPLOYEE KPI AVG(HOURS_BETWEEN(SOURCE(20_DOCS_EVENT.EVENTTIME), TARGET(20_DOCS_EVENT.EVENTTIME))) This calculation works, but it calculates all days, how can I count only working days?
Hi Artem, welcome to the Celonis Community! In order to count only working days, you need to use REMAP_TIMESTAMPS to calculate the time difference between the SOURCE and TARGET timestamps, instead of HOURS_BETWEEN. For the KPI, you would do: AVG( REMAP_TIMESTAMPS ( TARGET("20_DOCS_EVENT"."EVENTTIME"), HOURS, WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY ) ) - REMAP_TIMESTAMPS ( SOURCE("20_DOCS_EVENT"."EVENTTIME"), HOURS, WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY ) ) ) This would count Monday to Friday as working days. If you have a factory calendar or a workday calendar (TFACS) table in your data model that should be used instead, you can use FACTORY_CALENDAR or WORKDAY_CALENDAR instead of the WEEKDAY_CALENDAR instead. For more information on this, check out the DateTime Calendars section in the documentation. Best David
thanks for the answer! i tried this: AVG( REMAP_TIMESTAMPS ( TARGET(20_DOCS_EVENT.EVENTTIME), DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY ) - REMAP_TIMESTAMPS ( SOURCE(20_DOCS_EVENT.EVENTTIME), DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY ) ) and so it works, but I need HOURS for WEEKDAY_CALENDAR function gives error How can I fix it?
If you replace DAYS with HOURS in your query, that should work then. Which Celonis version are you working on? Best David
It works ) thx! version 4.3.0
Okay, cool! However, in version 4.3, you need to take care because the REMAP_TIMESTAMPS works on full days only when you specify only days, and no times. It is described and solved in this post, you might want to take a look at this: Remap timestamps without rounding Cheers David
Community Code of ConductAccessibility statement