As a work-around I realised that process explorers can do what I need, albeit in a not so user-friendly way. Here's how I did it:

 

1: Add a process explorer to a sheet

2: Change the dimension used in the process explorer to only show the 2 activities between which you want to measure the times:

CASE WHEN "ACTIVITY"."NAME" IN (A','B')

 THEN "ACTIVITY"."NAME"

 ELSE NULL

END

3: Show the time between the two activities to calculate the average time between the two. To calculate the average total time per case between the two status changes, I created the following connection KPI:

SUM(HOURS_BETWEEN(SOURCE("ACTIVITY"."DATETIME"),TARGET("ACTIVITY"."DATETIME")) / 24)

/

COUNT(DISTINCT SOURCE("ACTIVITY"."CASEID")

)

 

As mentioned, this is not the ideal solution, but for the short term it works.

If anyone has a better route to solve this issue then please let me know!

 

Hi Joos, if I understand your question correctly you could solve it using SOURCE/TARGET in an OLAP table.

 

You could create a table with dimension

SOURCE("ACTIVITY"."NAME", REMAP_VALUES ( "ACTIVITY"."NAME" , 'A' ,'A'], 'B' ,'B'], NULL ))

and use the (slightly modified) connection KPI you've created as a KPI, see documentation of the SOURCE/TARGET operators.

 

Does this give you the result you're looking for? Good luck!

Cheers, Lucas

Hi @lucas.van.d12 , thanks a lot for your answer!

 

I tried this in both an OLAP table and a single metric (which would be my end goal), however with this code I get the following error:

 

SUM(

 HOURS_BETWEEN(

   SOURCE("ACTIVITY"."DATETIME",

     REMAP_VALUES (

       "ACTIVITY"."NAME" ,

       ;'A','A'],

       NULL

     )

   ),

   TARGET("ACTIVITY"."DATETIME",

     REMAP_VALUES (

       "ACTIVITY"."NAME" ,

      'B','B'],

     NULL

     )

   )

 )

)

 

 

I also get this when I put the SOURCE and TARGET functions both as 2 different dimensions.

Removing the REMAP_VALUES at either the SOURCE or TARGET call resolves the issue but I need both.

We're running CPM 4.6

I can't seem to edit my own post but the error is as follows:

 

'No common parent between tables could be found, please check your schema. The tables $$EDGE_TABLE_0$$ and $$EDGE_TABLE_1$$ do not have a common parent in the schema.

Hi Joos,

 

That is a common issue (the documentation explains why, in case you didn't find it yet), you can either solve it by aggregating on case level before doin your HOURS_BETWEEN() but that won't give you all loops (only first-last, last-last, etc).

 

I think you should create a source-target table containing only the activities you're interested in, i.e. using the same filter on source and target, and use a statement like this (use the same filter statement in each part of your statement):

SUM(CASE WHEN SOURCE(..) = 'A' AND TARGET(..) = 'B' TEN HOURS_BETWEEN(..) ELSE 0.0 END)

 

That should do the job, please let me know if it worked for you :)

Thanks a lot Lucas! After a bit of puzzling I got the expected results.