thanks for reaching out!
In the following, I will explain you one way to solve this problem. We will create the following OLAP Table:
_CASE_KEY: Add the case key as a dimension:
condition: As a first KPI, we want to add the flagging or not flagging condition. This is the hard part of the problem. In the case below, I have chosen the activity ‘Change Quantity’ to be your “Activity A”. You can simply substitute the PQL code with your desired “Activity A”.
*CASE WHEN *
MATCH_PROCESS(NODE[‘Change Quantity’] as src,NODE[‘Change Quantity’] as trg CONNECTED BY EVENTUALLY [ src, trg ]) = 1
MATCH_PROCESS(NODE[‘Change Quantity’] as src,NODE[‘Change Quantity’] as trg CONNECTED BY DIRECT [ src, trg ]) = 0
CALC_REWORK("_CEL_P2P_ACTIVITIES_EN".“ACTIVITY_EN” = ‘Change Quantity’) >= 2
*ELSE ‘no flag’ *
explanation of this KPI:
We want to flag a row in the OLAP table if the Activity A is followed by another instance of the Activity A (in the process flow of one CASE ID), BUT Activity A is not directly followed by another instance of Activity A (which means that we have at least one other activity like B,C,D,E… in between them), AND there are at least two instances of the Activity A for one CASE ID. If this is correct, then we apply a FLAG, if not then we apply NO FLAG.
Activity: Next, we want to add a KPI simply in form of an activity. This is the third column in our OLAP table:
lagged activity: Last, we add the respective successor activity to every activity displayed in the third column. Simply add a KPI stating:
ACTIVITY_LEAD ( “_CEL_P2P_ACTIVITIES_EN”.“ACTIVITY_EN” )
The table is done. We can now filter on specific CASE IDs that are flagged.
We can also add conditional formatting. Let’s stick to the example above. Our target Activity A is still ‘Change Quantity’. In the OLAP settings, we can choose to mark all ‘Change Quantity’ activities in the third column e.g. yellow background.
If we now filter on a specific CASE ID, we see that the fifth and seventh (last) activity in the process flow of this CASE ID was the Activity ‘Change Quantity’:
Last, we can add a process explorer to the analysis sheet to have a visually perfect overview of the process flow of the case:
From this we can easily see that the Quantity was changed once, then the invoice was booked and then the quantity was changed again.
I hope this procedure solves your problem!
Your Celonis Team