If you are looking for eventime of an activity before and after you can use ACTIVITY_LEAD / ACTIVITY_LAG.
CASE WHEN ""ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start' THEN
ACTIVITY_LEAD ( "ACTIVITY_TBL"."EVENTTIME",1 ) END
That's actually not what I'm looking for, I've the eventtime of each activity, I need to count the activities happened before, during and after two specific activities, so before subprocess start, between start and end and after subprocess end. I don't think that activity lead or lag can actually help here
Hi @federico.spara,
I think that your quest can be easily solved using the INDEX_ACTIVITY_ORDER function together with a PU_COUNT function to count the total number of activities. This function easily returns the position of that activity in the activity table. You can easilly do this using an OLAP table, having the Case ID as dimension column, and add three columns called 'before', 'during' and 'after'.
# Activities before sub-process
-- Get the first 'subprocess start' activity index available per case
PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM") -1, "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start')
-- minus 1, since index 3 means that 2 preceded
# Activities during sub-process
-- detract index of start activity from end activity
PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess end') -
PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start')
# Activities during sub-process
-- detract end-activity index from total activities
PU_COUNT(CASE_TABLE, "ACTIVITY_TBL"."ACTIVITY_NM") -
PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess end')
Hope this helps.
Kind regards,
Jan-peter
Hi @federico.spara,
I think that your quest can be easily solved using the INDEX_ACTIVITY_ORDER function together with a PU_COUNT function to count the total number of activities. This function easily returns the position of that activity in the activity table. You can easilly do this using an OLAP table, having the Case ID as dimension column, and add three columns called 'before', 'during' and 'after'.
# Activities before sub-process
-- Get the first 'subprocess start' activity index available per case
PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM") -1, "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start')
-- minus 1, since index 3 means that 2 preceded
# Activities during sub-process
-- detract index of start activity from end activity
PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess end') -
PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess start')
# Activities during sub-process
-- detract end-activity index from total activities
PU_COUNT(CASE_TABLE, "ACTIVITY_TBL"."ACTIVITY_NM") -
PU_FIRST(CASE_TABLE, INDEX_ACTIVITY_ORDER("ACTIVITY_TBL"."ACTIVITY_NM"), "ACTIVITY_TBL"."ACTIVITY_NM" = 'subprocess end')
Hope this helps.
Kind regards,
Jan-peter
That's a point, but unfortunately it's not working, since I'm aggregating on two other columns in order to count the occurences of specific activities. My activity table is something like this:
ACTIVITY_NM COL1 COL2 EVENTTIME
name1 in op 02/02/2020
name2 in tel 02/02/2020
name3 in op 03/02/2020
subprocess start - - 04/02/2020
name4 out tel 05/02/2020
subprocess end - - 05/02/2020
name4 out tel 06/02/2020
Hence, my OLAP should be something like this:
COL1 COL2 BEFORE DURING AFTER
in op 2 0 0
in tel 1 0 0
out tel 0 1 1
