Calculate Time between two activities

HI,

I have a very peculiar situation. I want to calculate time between two activities but also exclude certain activities in between these two. I tried using SOURCE and TARGET but couldn’t succeed. Please let me know how these can be solved. Ideal situation is attached below in the chart.

Thanks,
Sravan

Hi Sravan,

you can use RUNNING_SUM to flag rows which you want to exclude or keep in your calculation. If you use those flags inside SOURCE/TARGET, you only return the throughput time between subsequent activities if they are flagged accordingly.

It would look something like this:

SUM(
CASE WHEN SOURCE ( 
    RUNNING_SUM(
        CASE 
        WHEN "Activities"."Activity" IN ( 'C' ) THEN 1
        WHEN "Activities"."Activity" IN ( 'D' ) THEN -1
        ELSE 0 
        END, 
        PARTITION BY ("Activities"."Case")
    )
) = 0

AND SOURCE ( 
    RUNNING_SUM(
        CASE 
        WHEN "Activities"."Activity" IN ( 'B' ) THEN 1
        WHEN ACTIVITY_LAG(REMAP_VALUES("Activities"."Activity", ['B', 'B'], NULL)) IS NOT NULL AND "Activities"."Activity"  IN ( 'A' ) THEN -1
        ELSE 0 
        END, 
        PARTITION BY ("Activities"."Case")
    )
) = 1


THEN HOURS_BETWEEN(SOURCE("Activities"."Timestamp"), TARGET("Activities"."Timestamp"))
ELSE 0.0 END
)

In the first RUNNING_SUM, we mark all activities between C and D with a 1, and state in the outer CASE WHEN that we only want to return the time of activities which were not flagged with a 1.
The second RUNNING_SUM is about the start and end activities B and A. We flag all entries between B and the A after the B with a 1. This might not yet be what you want; as there are multiple A activities in your example, such that “calculating time between B and A” could be interpreted in multiple ways. The above formula assumes that we calculate the time between B and the first A after the B. It does currently not work if you have multiple B’s in your case (what should happen in this case?).

Hope this already helps.
David

Hi David,

Thanks for the reply. Unfortunately i do not see running_sum in our Celonis IBC for some reason.I can only see running_total.Is there any other method i can use?

Also, About your question …B never occur multiple times but A can occur multiple times. So looking for the time period between B and the next A activity.
image

Hi Sravan,

are you using the Full IBC, or a Hybrid version? In the full IBC, the function should work, if you are on Hybrid you might have to wait until the next release, as this function is relatively new.

Best
David

Hi David,

I think it is a Hybrid version but not so sure on that.

Thanks,
Sravan

Hi Sravan,

I think you could go for a simpler approach.
Why not just get the Time between B and A and substract the time between c and D?

AVG(
    CALC_THROUGHPUT(
        FIRST_OCCURRENCE['B'] TO 
        LAST_OCCURRENCE['A'], 
        REMAP_TIMESTAMPS("ACTIVITIES"."EVENTTIME", DAYS)
    ) - 
    CALC_THROUGHPUT(
        FIRST_OCCURRENCE['C'] TO 
        LAST_OCCURRENCE['D'], 
        REMAP_TIMESTAMPS("ACTIVITIES"."EVENTTIME", DAYS)
    )
)

Best regards

Hi Syrex-o,

Thanks for your reply.I have activity A occurring multiple times in the process.So i need to calculate Time where A occurs anytime after B. As you can see in my example above i had A occurring at the start and end of my process.I don’t want to calculate either of those occurrences but need to calculate that happened in between.

Thanks,
Sravan