Hi @Sugano,
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

image799180 26.3 KB

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:
MAX(INDEX_ACTIVITY_TYPE (ACTIVITY_TABLE.ACTIVITY))
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:

Best regards,
Viana

---

Thank you for your help.
I followed your advise. However, I could not use INDEX_ACTIVITY_TYPE.
Our Celonis says,Invalid operator type INDEX_ACTIVITY_TYPE.
We use Celonis 4.4 version.
Do you have any idea?

---

For Celonis 4.4 version you can use the function ACTIVATION_COUNT instead of INDEX_ACTIVITY_TYPE. With this everything should work fine.

---

Dear Ms. KALVERSBERG,
Regarding your answer:
How could one retrieve the case ID of the most reworked case?
image746239 11.5 KB
I tried multiple PQL queries but it is not working at all I was trying to have a column to show the ID of the ticket with the most repeated activity for the given activity. This would help to drill down on this worst-of-the-worst case.
Would you have a solution for this situation?
Looking forward to hearing from you!

---

Hi,
in case you want to have the case id from a single statement, you can use following formula:
PU_FIRST("CASE_TABLE", "_CEL_XX_ACTIVITIES"."_CASE_KEY",
ACTIVATION_COUNT("_CEL_XX_ACTIVITIES"."ACTIVITY_EN") =
GLOBAL(MAX(ACTIVATION_COUNT("_CEL_XX_ACTIVITIES"."ACTIVITY_EN"))))

Otherwise, you can simply create a OLAP table with CASE_ID as dimension and ACTIVATION_COUNT() as a KPI and sort it ascending.
Best,
Viana

---

Hey,
Thank you so much.
In my case it does not work. What if there is no child and parent table? What if they are both the same table?

---