Hi Emiel,

Can you provide an info on what the system that you're taking data from? Usually for such purposes we're using tables that tracks changes in specific items f.e. in SAP environment those are tables called CDHDR and CDPOS (header / item for changes). If there's no specific, global table for such things, try to get a log for specific item - that should contain info that on date DD,MM.YYYY some user took over the case. Thank you need to clean it and generate as many activities for case as these changes with added information which user took it from which. That way you can start calculating KPI's mentioned in your post.

Best Regrads,

Mateusz Dudek

Hi Mateusz,

thanks for your quick reply.

The relevant data is already available in Celonis. We've a data model that contains an activity-tabel including Activities, Timestamps, USER_ID, etc. on Activity level so we know who is performing a certain activity. I'm 'just' looking for the PQL to calculate the amount of handovers. We know that activities go from USER_ID1, to USER_ID2, to USER_ID3 and back to USER_ID1 (or other variants). By doing so we want to know the number of colleagues involved in a case (avg across all cases, absolute on case-by-case, trends, etc.).

Off topic; we needed to anonymize the USER_IDs to avoid capturing personal data so we made a python script that anonomyzes and pseudonymizes on case level. The end result is (example): USER_ID 1 = Pseudo123ABC and USER_ID2 = Pseudo123ABC within Case_ID 456. The script went through the entire dataset and did this case by case. So another example: for Case_ID 987 USER_ID1 = Pseudo765XYZ and USER_ID2 = Pseudo345KLM, etc. By doing so we can still we can still identify handovers within a case (albeit anonymized but that's OK). At least that's the idea.

Hope this helps in getting some context behind the question.

In the meantime I found the solution by simply using the PU_COUNT_DISTINCT PQL function. More information >> here

I used the first example

Column 1: "companyDetail"."companyCode"

Column 2: PU_COUNT_DISTINCT ( "companyDetail" , "caseTable"."value" )

To AVG the handovers across all cases I simply added AVG to the equation > "AVG(PU_COUNT_DISTINCT( "companyDetail" , "caseTable"."value" )

Works as expected.