Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4
Description
This function calculates working days between two dates based on a TFACS style calendar table.
The calendar table can be configured in the Data Model Editor and applies to all calls of WORKDAYS_BETWEEN in the Data Model.
The calendar table has to have the following layout:
- Calendar ID: Identifier of the working calendar
- Year: Year of the calendar
- Month 1: Working days of January bit encoded. A 1 is a working day. A 0 is a day off. Example of January 2019 with the weekends off: 1111001111100111110011111001111
- Month 2: Working days of February.
- ...
- Month 12: Working days of December
WORKDAYS_BETWEEN ( calendar_id, start, end )
- calendar_id: the ID defines which calendar out of the calendar table is used.
If the calendar id does not exist in the calendar table, WORKDAYS_BETWEEN assumes that only the weekends are days off.
Example
Here WORKDAYS_BETWEEN is used without an existing working calendar. Therefore it assumes that all days, except
weekends are working days.
Query
Column1
WORKDAYS_BETWEEN ( "Table1"."Calendar_ID" , "Table1"."Column1" , "Table1"."Column2" )
Input
Table1
Column1 : DATE | Column2 : DATE | Calendar_ID : STRING |
---|---|---|
Sat Apr 15 2017 00:00:00.000 | Sun Apr 16 2017 00:00:00.000 | 'Does not exist Calendar ID' |
Sun Apr 16 2017 00:00:00.000 | Mon Apr 17 2017 00:00:00.000 | 'Does not exist Calendar ID' |
Mon Apr 17 2017 00:00:00.000 | Tue Apr 18 2017 00:00:00.000 | 'Does not exist Calendar ID' |
Output
Result
Column1 : INT |
---|
1 |
1 |
1 |