Skip to main content
Question

Time difference between multiple occurrences of activities

  • March 4, 2021
  • 3 replies
  • 59 views
.
Level 3
In my activities table, I have cases like create incident A B C B A B Now I want to calculate the time difference between create incident and every occurrence of B. Example: (create incident and B (first occurrence), create incident and B(second occurrence), create incident and B(third occurrence)). After that, I want to calculate the overall average of these time intervals.

3 replies

D
Level 1
Forum|alt.badge.img+2
Hi @Abhitndn you can make use of the ACTIVITY_LAG function here. The idea is the following: For every B, go up the activities of the case and find the row with activity Create Incident. Take this timestamp and compare it to the one of the current B. CASE WHEN "Activities"."Activity" = 'B' THEN HOURS_BETWEEN( ACTIVITY_LAG( CASE WHEN "Activities"."Activity"='Create Incident' THEN "Activities"."Eventtime" ELSE NULL END ), "Activities"."Eventtime" ) ELSE NULL END You can put an AVG around this to get the average time. If there are multiple Create Incident activities inside one case, this formula will, for every B, always take the most recent one, i.e. the last one which occurred before this B. Best David
.
Level 3
  • .14600843
  • Author
  • Level 3
  • March 8, 2021
Thanks a lot, David that worked perfectly.
A
Level 5

@david.beche12 

 

I have a similiar problem. I have written the below code where activity A occurs multiple times in the case and I am trying to find the time difference between the multiple occurrences. But I am getting the result in negative like -52 or -7. Can you let me know how I can correct this

 

CASE WHEN

"_CEL_C2R_ACTIVITIES"."ACTIVITY_EN" = 'A' THEN

minutes_BETWEEN(

     ACTIVITY_LEAD( CASE WHEN "_CEL_C2R_ACTIVITIES"."ACTIVITY_EN"='A' THEN "_CEL_C2R_ACTIVITIES"."Eventtime" ELSE NULL END ),

    "_CEL_C2R_ACTIVITIES"."Eventtime" )

ELSE NULL END

 

 

Community Code of ConductAccessibility statement