Question
Count working days for Source and target function
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?
+2Hi 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
Davidthanks 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?
+2If you replace DAYS with HOURS in your query, that should work then.
Which Celonis version are you working on?
Best
David
+2Okay, 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.