The first step in creating such a table is to create an OLAP table with the dimension ”_CEL_P2P_ACTIVITIES_EN”.”ACTIVITY_EN” from the activity table.
In this example the activity table is called ”_CEL_P2P_ACTIVITIES_EN” and the corresponding activity column is called ”_CEL_P2P_ACTIVITIES_EN”.”ACTIVITY_EN”.
The next step is to set a component filter for the OLAP table.
The following formula needs to be typed into load script:
filter (CASE WHEN INDEX_ACTIVITY_TYPE (”_CEL_P2P_ACTIVITIES_EN”.”ACTIVITY_EN”) > 2 THEN 1.0 ELSE 0.0 END) = 1
The INDEX_ACTIVITY_TYPE operator returns for every activity in every case, how many times, at a given point in a process this activity has already occurred.
Therefore, if we set the condition that the minimum number of reoccurrences should be bigger than 2, we get all the activities which were repeated more than 2 times for each case.
The last step is to create an KPI with the following formula:
With this formula only the highest number of repetitions for each activity is listed.
At the end the solution table should look something like this: