Count if the instances of Activity A are followed by other activity within a particular scope

Hi Team,

We are trying to identify and associate flag to a particular activity based on whether there were some other set of activities post it or not till the next instance of the activity. So for example we want to associate a flag to Activity A if in a case journey activity B, C, D or E are occuring any time between the next occurence of Activity A within the case.

We intend to track this for each instance of activity A taking place within a case and associate a flag whether the activities B,C, D or E took place or not in an OLAP table.

We are looking for any way in which we can set the scope from the instance of activity A taking place till its next instance and check within this scope whether the other activities took place.

Thanks in Advance.
Soham Potdar

Hi @Soham_Potdar,

thanks for reaching out!

In the following, I will explain you one way to solve this problem. We will create the following OLAP Table:

  1. _CASE_KEY: Add the case key as a dimension:

“_CEL_P2P_ACTIVITIES_EN”."_CASE_KEY"

  1. 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
AND
MATCH_PROCESS(NODE[‘Change Quantity’] as src,NODE[‘Change Quantity’] as trg CONNECTED BY DIRECT [ src, trg ]) = 0
*AND *
CALC_REWORK("_CEL_P2P_ACTIVITIES_EN".“ACTIVITY_EN” = ‘Change Quantity’) >= 2
THEN ‘flag’
*ELSE ‘no flag’ *
END

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.

  1. Activity: Next, we want to add a KPI simply in form of an activity. This is the third column in our OLAP table:

“_CEL_P2P_ACTIVITIES_EN”.“ACTIVITY_EN”

  1. 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.


Extras:

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!

Best,

Your Celonis Team