I have the following table structure:
activity -- incident -- user
Activity - as name suggests, has activities in it (in this case, any updates of an incident over time) Incident - contains header data of a case User - contains info about user who is owner of the incident
In Celonis EMS, I'm trying to create an OLAP table, where I would have the incident category as a dimension, and as a KPI I would like to be able to see throughput time (start to end) for any given category, but with a caveat - I need to see two different columns of this KPI, one where user is a VIP and other column where user isn't a VIP.
I have tried to create the following code:
MEDIAN( CASE WHEN "user"."VIP" = 1 THEN CALC_THROUGHPUT( CASE_START TO CASE_END, REMAP_TIMESTAMPS("activity"."SYS_CREATED_ON", SECONDS) ) ELSE NULL END )/60/60
And this code works, but it gives completely wrong number of 113, where in reality it should be somewhere around 4.44.
This is the PQL code I use for getting number of incidents for a given category:
COUNT(DISTINCT( CASE WHEN "USER"."VIP" = 1 THEN "incident"."sys_id" ELSE NULL END ))
Anyone has any clue where I might be wrong?
