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.
Page 1 / 1
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.
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
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
Thanks a lot, David that worked perfectly.
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
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.