Skip to main content
Question

Business hours calculation

  • January 25, 2022
  • 6 replies
  • 13 views
.
Level 3

Is there a way to calculate all KPIs by considering working days only? e.g. 5 workdays per week? e.g. average case duration would be lesser considering 5 days instead of 7.

    6 replies

    D
    Level 0

    Hello,

     

    I'm affraid that you cannot change the default KPI's. However can create a new ones, and combine WEEKDAY_CALENDAR with CASE WHEN and REMAP_TIMESTAMPS. Sample:

     

    Calculate the number of working days between two dates using the REMAP_TIMESTAMPS function. It is assumed that working days are from Monday to Friday (with 24h a day).

     

    REMAP_TIMESTAMPS (

       "TimestampTable"."DateColumn2" ,

       DAYS ,

       WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )

    )

    -

    REMAP_TIMESTAMPS (

       "TimestampTable"."DateColumn1" ,

       DAYS ,

       WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )

    )

     

    Best Regards,

    Mateusz Dudek

    .
    Level 3
    • .146004175
    • Author
    • Level 3
    • February 2, 2022

    Hello,

     

    I'm affraid that you cannot change the default KPI's. However can create a new ones, and combine WEEKDAY_CALENDAR with CASE WHEN and REMAP_TIMESTAMPS. Sample:

     

    Calculate the number of working days between two dates using the REMAP_TIMESTAMPS function. It is assumed that working days are from Monday to Friday (with 24h a day).

     

    REMAP_TIMESTAMPS (

       "TimestampTable"."DateColumn2" ,

       DAYS ,

       WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )

    )

    -

    REMAP_TIMESTAMPS (

       "TimestampTable"."DateColumn1" ,

       DAYS ,

       WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )

    )

     

    Best Regards,

    Mateusz Dudek

    Hi @deleted deleted 

    Thanks for your answer.

    So if I want to calculate average throughput times considering 9-5 pm workdays, I'd have to use AVG(CALC_THROUGHPUT(FIRST_OCCURRENCE['Event1'] TO LAST_OCCURRENCE['Event2'], REMAP_TIMESTAMPS("eventlog"."TIME", DAYS,WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY 9:00-17:00))))

     

    Is this correct?

    D
    Level 0

    Hi @deleted deleted 

    Thanks for your answer.

    So if I want to calculate average throughput times considering 9-5 pm workdays, I'd have to use AVG(CALC_THROUGHPUT(FIRST_OCCURRENCE['Event1'] TO LAST_OCCURRENCE['Event2'], REMAP_TIMESTAMPS("eventlog"."TIME", DAYS,WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY 9:00-17:00))))

     

    Is this correct?

    @1460068767 Looks good to me, however it's hard to tel if the logic and syntax are correct without extensive testing - let me know if that worked :)

     

    Please note that all events done in those days, but outside the working hours may not been considered as working day activities - however I'm not sure about that.

     

    Best regards,

    Mateusz Dudek

    .
    Level 3
    • .146004175
    • Author
    • Level 3
    • February 4, 2022

    Hi @deleted deleted 

    Thanks for your answer.

    So if I want to calculate average throughput times considering 9-5 pm workdays, I'd have to use AVG(CALC_THROUGHPUT(FIRST_OCCURRENCE['Event1'] TO LAST_OCCURRENCE['Event2'], REMAP_TIMESTAMPS("eventlog"."TIME", DAYS,WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY 9:00-17:00))))

     

    Is this correct?

    Hi @deleted deleted 

    Seems like Celonis has this thing inbuilt. I found the Calendars option in the process data model section, where we can define a calendar. Thanks for your reply.

    D
    Level 0

    Hi @deleted deleted 

    Thanks for your answer.

    So if I want to calculate average throughput times considering 9-5 pm workdays, I'd have to use AVG(CALC_THROUGHPUT(FIRST_OCCURRENCE['Event1'] TO LAST_OCCURRENCE['Event2'], REMAP_TIMESTAMPS("eventlog"."TIME", DAYS,WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY 9:00-17:00))))

     

    Is this correct?

    Hi @1460068767, 

     

    About that build-in calendar - i've tried to use it on on-prem version (4.6) as there's no weekday_calendar pql function. However it doesn't work, so it's hard to recommend it.

    Have you solved your problem using that?

     

    Best Regards,

    Mateusz Dudek

    .
    Level 3
    • .146004175
    • Author
    • Level 3
    • February 21, 2022

    Hi @deleted deleted 

    Thanks for your answer.

    So if I want to calculate average throughput times considering 9-5 pm workdays, I'd have to use AVG(CALC_THROUGHPUT(FIRST_OCCURRENCE['Event1'] TO LAST_OCCURRENCE['Event2'], REMAP_TIMESTAMPS("eventlog"."TIME", DAYS,WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY 9:00-17:00))))

     

    Is this correct?

    Hi @deleted deleted 

    I am using the Celonis free plan on cloud, so not sure about on prem. But the calendars option did solve my problem.

    Community Code of ConductAccessibility statement