Business Calendar

From QPR ProcessAnalyzer Wiki
Revision as of 19:50, 12 February 2023 by Ollvihe (talk | contribs) (→‎Business Calendars in SQL Expressions)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

The business calendar defines a weekly calendar for working/office/factory/etc. time, which can be used in the duration calculation to get durations which only covers the working time (instead of 24 hour per day). Each model can have an own business calendar and in addition, business calendars can be defined in custom expressions. In dimensions and measures calculating durations, there is a Use business calendar setting available determining whether durations are calculated 24 hour per day or using the business calendar of the model.

Defining Business Calendar

Business calendar can be defined for a model as follows: Open Workspace, select project where the model is located, open the Models tab, click the model and select Business calendar in the context menu. In the dialog, the Weekly Business Time tab allows to define the weekly schedule where each row is a period within a day. Note that the weekly schedule periods must not overlap each other.

The Holidays tab allows to define days, when there is exceptionally no business hours. If you need to set the ending time to a midnight, use value 12:00 AM / 00:00 (i.e. same as the day begin time). If there is a need for a period that spans to multiple days, define them as separate rows where the previous ends at midnight and the next period starts at the beginning of the day.

Business Calendars in SQL Expressions

In SQL expressions, business calendars can be used in the DurationBetweenDates function. As can be seen in the examples below, the 4. parameter defines the business calendar, and the #expr{} tag needs to be used there.

Here is an example, how to use the model's default calendar:

DurationBetweenDates("day", FromTimeStamp, ToTimeStamp, #expr{_model.DefaultCalendar})

Here is an example, how to use a calendar defined entirely in the expression ("inline calendar"):

DurationBetweenDates("day", FromTimeStamp, ToTimeStamp, #expr{
    "WeeklyWorkingHours": [
      #{ "WeekDay": 1, "StartHour": 8, "EndHour": 16 },
      #{ "WeekDay": 2, "StartHour": 8, "EndHour": 11 }

The inline calendars allow to use different calendar than in the model settings, and also allows to use multiple calendars for the same model. The inline calendars are defined as in-memory expressions, so for more information how to define inline calendars, see the next chapter.

Business Calendars in In-memory Expressions

Defining Inline Business Calendar

In the expression language, a business calendar is created with the BusinessCalendar function using the following calendar definition:

  "WeeklyWorkingHours": [
    #{ "WeekDay": 1, "StartHour": 8, "EndHour": 16 },
    #{ "WeekDay": 2, "StartHour": 8, "EndHour": 11 },
    #{ "WeekDay": 2, "StartHour": 12, "EndHour": 17 },
    #{ "WeekDay": 3, "StartHour": 8, "EndHour": 16 },
    #{ "WeekDay": 4, "StartHour": 8, "EndHour": 16 },
    #{ "WeekDay": 5, "StartHour": 8, "EndHour": 15 }
  "ExceptionDays": [ "2020-12-06", "2020-12-24", "2020-12-25", "2020-12-26", "2021-01-01", "2021-01-06" ]

This example defines the following weekly schedule: Monday 8-16, Tuesday 8-11 and 12-17, Wednesday 8-16, Thursday 8-16 and Friday 8-15. There is no working time in Saturday and Sunday, as they are not defined in the example calendar.

The structure in the business calendar initialization has the following properties:

  • WeeklyWorkingHours: Defines the weekly working calendar. The items in the array are individual working periods. One working period can span only within the same day. There can be several working periods in the same day. The WeekDay is defined as a number between 0 (Sunday) and 6 (Saturday). The StartHour and EndHour needs to be between 0 and 24. If no weekly schedule is defined, 24h/days calendar is assumed.
  • ExceptionDays: List of dates, when there is exceptionally no working time (e.g. holidays). Exception days are defined as a string array where each day is defined using format yyyy-MM-dd. Defining exception days is not mandatory.

Calculating Duration between Timestamps

Durations based on the business calendar are calculated by using the TimeDiff function that is available in the Business Calendar object:

let calendar = BusinessCalendar(#{
  "WeeklyWorkingHours": [
    #{ "WeekDay": 1, "StartHour": 8, "EndHour": 16 },
    #{ "WeekDay": 2, "StartHour": 8, "EndHour": 11 },
    #{ "WeekDay": 2, "StartHour": 12, "EndHour": 17 }
  "ExceptionDays": [ "2020-12-06", "2020-12-24", "2020-12-25", "2020-12-26", "2021-01-01", "2021-01-06" ]
calendar.timeDiff(DateTime(2020, 12, 1), DateTime(2021, 1, 1));

Duration Calculation using Eventlog Objects

Process mining objects in the Eventlogs API have the following properties and functions available for duration calculation:

Object type 24/7 durations Business calendar durations
  • Duration property
  • DurationBetweenEvents function
  • Duration(businessCalendar) function
  • DurationBetweenEvents function (5. parameter)
  • AverageDuration property
  • MedianDuration property
  • DurationStandardDeviation property
  • AverageDuration(BusinessCalendar) function
  • MedianDuration(BusinessCalendar) function
  • DurationStandardDeviation(BusinessCalendar) function
  • Duration property
  • Duration(BusinessCalendar) function