Skip to main content
Solved

Calculate activity status duration, ignoring other activities' status changes

  • June 4, 2021
  • 6 replies
  • 17 views
joos.buijs
Level 10
Forum|alt.badge.img+2

Hi Celopeers :)

 

We have a specific use case, based on JIRA data. For a case a limited set of activities (~7, JIRA stories) need to be performed. For each activity (/story) we know when it was ready for pickup, in progress, and done (or rejected). If we would like to calculate the total time the activity/story spent in the status 'in progress' I was thinking of something like this:

 

SUM(

 CASE WHEN "TABLE_STATUS"."STATUS" = 'In progress'

   THEN HOURS_BETWEEN("TABLE_STATUS"."STATUSDATETIME",ACTIVITY_LEAD("TABLE_STATUS"."STATUSDATETIME")) / 24 --to get fractional days

   ELSE 0.0

 END

)

 

Where the dimension could be the different activity/story names for instance.

 

However, the activity_lead often is a very different activity (/JIRA story), hence the calculation fails.

I also cannot use a process pattern matching using first and last occurrence, as an activity can be 'in progres' multiple times and I would need to have the total time spent in progress.

 

I hope my issue is clear, and that someone has an answer for me!

Best answer by lucas.van.d12

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 :)

6 replies

joos.buijs
Level 10
Forum|alt.badge.img+2
  • joos.buijs
  • Author
  • Level 10
  • 129 replies
  • June 7, 2021

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!

 

lucas.van.d12
Level 5
Forum|alt.badge.img+7
  • lucas.van.d12
  • Level 5
  • 6 replies
  • June 16, 2021

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

joos.buijs
Level 10
Forum|alt.badge.img+2
  • joos.buijs
  • Author
  • Level 10
  • 129 replies
  • June 18, 2021

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

joos.buijs
Level 10
Forum|alt.badge.img+2
  • joos.buijs
  • Author
  • Level 10
  • 129 replies
  • June 18, 2021

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.

lucas.van.d12
Level 5
Forum|alt.badge.img+7
  • lucas.van.d12
  • Level 5
  • 6 replies
  • Answer
  • June 21, 2021

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 :)

joos.buijs
Level 10
Forum|alt.badge.img+2
  • joos.buijs
  • Author
  • Level 10
  • 129 replies
  • June 22, 2021

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


Community Code of ConductAccessibility statement