Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

CALC_THROUGHPUT

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

Description

Throughput is used to calculate, for each case, the time between two activities. From which activity the calculated throughput time should start and at which it should end can be configured through range specifiers.

Syntax
CALC_THROUGHPUT ( begin_range_specifier TO end_range_specifier, timestamps [, table.column ] )

  • begin_range_specifier: FIRST_OCCURRENCE['activity'] | LAST_OCCURRENCE['activity'] | CASE_START | ALL_OCCURRENCE['']

    • FIRST_OCCURRENCE['activity']: Throughput time starts at the first occurrence of the specified activity type.

    • LAST_OCCURRENCE['activity']: Throughput time starts at the last occurrence of the specified activity type.

    • CASE_START: Throughput time starts at the first activity of the case.

    • ALL_OCCURRENCE['']: Has the same meaning as CASE_START. The string parameter is ignored, but has to be specified. [ DEPRECATED SINCE 4.6 Use CASE_START instead. ]

  • end_range_specifier: FIRST_OCCURRENCE['activity'] | LAST_OCCURRENCE['activity'] | CASE_END | ALL_OCCURRENCE['']

    • FIRST_OCCURRENCE['activity']: Throughput time ends at the first occurrence of the specified activity type.

    • LAST_OCCURRENCE['activity']: Throughput time ends at the last occurrence of the specified activity type.

    • CASE_END: Throughput time ends at the last activity of the case.

    • ALL_OCCURRENCE['']: Has the same meaning as CASE_END. The string parameter is ignored, but has to be specified. [ DEPRECATED SINCE 4.6 Use CASE_END instead. ]

  • timestamps: Integer column which has to be joinable to the activity table, often REMAP_TIMESTAMPS is used to convert a TIMESTAMP column.

  • table.column: Column which has to be joinable to the same activity table as provided timestamps column. (Since: CELONIS 4.6)

If start and end activity is conflicting, meaning the end activity is before the start activity the throughput calculation returns null for the conflicting case. Null is also returned if a case has only one activity.

If the activity name used in FIRST_OCCURRENCE or LAST_OCCURRENCE does not exist, then a warning is displayed. In this scenario the result value will be null. (Since: CELONIS 4.6)

Examples

[1] The throughput is calculated for the first A activity and the first B activity.

Query

Column1

CALC_THROUGHPUT (
    FIRST_OCCURRENCE [ 'A' ]
    TO
    FIRST_OCCURRENCE [ 'B' ] ,
    REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES )
)

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : INT

1

[2] Here there is an attempt to calculate the throughput between last B and last A. Since the starting activity (last B) comes after the ending activity (last A), there is a conflict and NULL is returned.

Query

Column1

CALC_THROUGHPUT (
    LAST_OCCURRENCE [ 'B' ]
    TO
    LAST_OCCURRENCE [ 'A' ] ,
    REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES )
)

Input

Output

Column1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : INT

null

[3] In the special case that start and end activity are the same, null is returned. In this example start and end are the same because the first and last activity A are pointing to the same activity log entry.

Query

Column1

CALC_THROUGHPUT (
    FIRST_OCCURRENCE [ 'A' ]
    TO
    LAST_OCCURRENCE [ 'A' ] ,
    REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES )
)

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'C'

Tue Jan 01 2019 13:03:00.000

Result

Column1 : INT

null

[4] The throughput is calculated for the last A activity and the last B activity.

Query

Column1

CALC_THROUGHPUT (
    LAST_OCCURRENCE [ 'A' ]
    TO
    LAST_OCCURRENCE [ 'B' ] ,
    REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES )
)

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : INT

4

[5] The throughput is calculated for the first A activity and the last B activity.

Query

Column1

CALC_THROUGHPUT (
    FIRST_OCCURRENCE [ 'A' ]
    TO
    LAST_OCCURRENCE [ 'B' ] ,
    REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES )
)

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : INT

7

[6] The throughput is calculated for the first A1 activity and the last B1 activity with a custom activity expression.

Query

Column1

CALC_THROUGHPUT (
    FIRST_OCCURRENCE [ 'A1' ]
    TO
    LAST_OCCURRENCE [ 'B1' ] ,
    REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) ,
    "Table1"."ACTIVITY" || '1'
)

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : INT

7

[7] The throughput is calculated from the beginning of the case till its end.

Query

Column1

CALC_THROUGHPUT ( CASE_START TO CASE_END , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) )

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : INT

7

[8] CALC_THROUGHPUT query using non-existing activity in FIRST_OCCURRENCE: Empty result and warning.

Query

Column1

CALC_THROUGHPUT (
    FIRST_OCCURRENCE [ 'X' ]
    TO
    LAST_OCCURRENCE [ 'B' ] ,
    REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES )
)

Input

Output

Table1

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:02:00.000

'1'

'B'

Tue Jan 01 2019 13:04:00.000

'1'

'A'

Tue Jan 01 2019 13:08:00.000

'1'

'B'

Tue Jan 01 2019 13:16:00.000

Result

Column1 : INT

null

Note

CALC_THROUGHPUT: Source Activity 'X' is not found in configured activity column.

See also: