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?

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

1 Like

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