Hi there!
I'm just begin with Celonis and i need to show the #tasks with the last timestamp.
My data model have several Case_ID with "n" tasks associated with each case_id and each task have "n" events associated to each task. Each event have a timestamp and user.
I want to show the number of tasks closed by each user, setting the criteria of closed task like the last date in timestamp of all events associated to that task.
I'm not suer if i've explain clear.......
Thanks a lot in advance!!!!
Hello Juan! Here is my suggestion:
Use PU_LAST to get the final activity associated with each case. Then, use a CASE statement to return the user name WHEN the activity timestamp is equal to the PU_LAST. Finally, pull up the distinct count of users per case and sum the resulting case-level count to get the overall total per user.
SUM(
PU_COUNT_DISTINCT( "CASE_TABLE",
CASE
WHEN PU_LAST( "CASE_TABLE", "ACTIVITY_TABLE"."EVENTTIME" ) = "ACTIVITY_TABLE"."EVENTTIME"
THEN "CASE_TABLE"."USERNAME"
ELSE NULL
END
)
)
Thank u so much, Chris!!!1 It's perfect!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
