Calculations on Activity Sequences
Description
This example shows how to identify and perform calculations on activity sequences containing more than two activities with PQL.
Identifying recurrent sequences containing e.g. unnecessary rework is an important requirement to ensure a smooth process handling. For example, the smooth processing of the purchase approval workflow is crucial to ensure a prompt delivery of ordered materials. As purchase orders often have to pass extensive approval procedures, the avoidance of unnecessary rework steps associated with the release process are therefore essential.
Challenge
For each case, show the occurring activity sequences that contain the activities 'Approval requested', 'Change PO for approval requested' and 'PO changed'.
The number of occurrences per case should be shown in a separate column.
The average throughput time for the activity sequence per case should be shown in a separate column.
Solution
[1] The activity sequences can be identified using the following query. In this example, case 1 and case 3 contain the specific activity sequence ('Approval requested' followed by 'Change PO for approval request' followed by 'PO changed'), and case 2 does not: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Step-by-Step Solution
In order to find (specific) activity sequences, we need to compare each activity with the following activities. This can be done with the ACTIVITY_LEAD operator. Now let's take a closer look on this operator.
[2] To be able to compare the current activity with the next and the second next activity, we need to have them in one line. We can do so by using ACTIVITY_LEAD: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[3] In order to get the specific activity sequences starting with activity 'Approval requested', we are applying a filter to the table: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[4] In the next step, we want only activity sequences starting with activity 'Approval requested' followed by activity 'Change of PO for approval requested' and ending with activity 'PO changed' in the second next activity. Therefore, we are applying an additional filter to the table: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[5] Next, we want to count the occurrences of the specific activity sequence in the table. To do so, we are using a COUNT aggregation and additionally concatenate the columns with activity names: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[6] In the last step, we additionally want to calculate the average throughput time for the activity sequences per case. For this purpose, we are using the AVG aggregation around the DATEDIFF function in a separate column: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|