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?
Page 1 / 1
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:
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
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?
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
Which Celonis version are you working on?
Best
David
It works ) thx!
version 4.3.0
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
It is described and solved in this post, you might want to take a look at this: Remap timestamps without rounding
Cheers
David
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.