Need help with calculating times between consecutive activities

Dear Matt,

Make an OLAP table and make the Case Key as dimension. Then make a KPI with the following PQL:

MAX(

CASE WHEN

INDEX_ACTIVITY_ORDER ( "CEL_Activity_Table"."_ACTIVITY_EN" ) <> 1 AND

"CEL_Activity_Table"."_ACTIVITY_EN" = ACTIVITY_LAG("CEL_Activity_Table"."_ACTIVITY_EN",1 )

THEN

DAYS_BETWEEN (ACTIVITY_LAG("CEL_Activity_Table"."_EVENTTIME",1 ), "CEL_Activity_Table"."_EVENTTIME")

ELSE NULL END)

This code makes sure that only the consecutive events (with same activity name) are getting passed to the DAYS_BETWEEN function, which then calculates the days between the two dates.

So for example:

001:Walk the Dog (not computed, no previous activity)

002:Walk the Dog (diff gets computed)

003:Walk the Dog (diff gets computed)

004: Walk the Cat (not computed)

005:Walk the Dog (not computed)

If you have any questions, feel free to reach out.

Hope it helps.

Yours,

Zsolt Borbély

Hi Zsolt! Thank you so much for your help on this! it is almost exactly what I need.

In the example you rewrote above:

001:Walk the Dog (not computed, no previous activity)

002:Walk the Dog (diff gets computed)

003:Walk the Dog (diff gets computed)

004: Walk the Cat (not computed)

005:Walk the Dog (not computed)

I would like the 5th event - Walk the Dog to be computed as it relates to the 3rd event.

I tried looking at using the third example from this documentation: https://docs.celonis.com/en/index_activity_order.html

INDEX_ACTIVITY_ORDER ( REMAP_VALUES ( "Table1"."ACTIVITY" , [ 'A' , NULL ] ) )

but was having difficulty.

I think I need to modify this part of the code to look at the last of the same activity, not just the prior activity.

"CEL_Activity_Table"."_ACTIVITY_EN" = ACTIVITY_LAG("CEL_Activity_Table"."_ACTIVITY_EN",1 )

do you have any recommendations?

Again thank you so much for getting me this far!

Dear Matt,

Right, in this case it is much simpler:

Add this as a component filter:

FILTER REMAP_VALUES ( "CEL_Activity_Table"."_ACTIVITY_EN", ['Walk The Dog', 'Walk The Dog'], NULL) IS NOT NULL;

The PQL of the KPI itself:

MAX(

DAYS_BETWEEN (ACTIVITY_LAG("CEL_Activity_Table"."_EVENTTIME",1 ), "CEL_Activity_Table"."_EVENTTIME")

)

Alternatively, you could use this as well without component filter:

MAX(

CASE WHEN

REMAP_VALUES ( "CEL_Activity_Table"."_ACTIVITY_EN", ['Walk the Dog', 'Walk the Dog'], NULL) IS NOT NULL

THEN

DAYS_BETWEEN (ACTIVITY_LAG("CEL_Activity_Table"."_EVENTTIME",1 ), "CEL_Activity_Table"."_EVENTTIME")

ELSE NULL END)

Does this solve your issue?

Yours,

Zsolt Borbély

Again - thank you so much for your assistance! I don't want to use filter because I do need other activities in the same component.

The remap IS NOT NULL solution seems to help not calculate the time between steps. But it does not skip over steps appropriately.

Example of what is happening:

walk the cat - 1/1/2023 - NULL

walk the dog - 1/2/2023 - 1

walk the dog - 1/3/2023 - 1

walk the cat - 1/4/2023 - NULL

walk the dog - 1/5/2023 - 1

Desired result:

walk the cat - 1/1/2023 - NULL

walk the dog - 1/2/2023 - NULL

walk the dog - 1/3/2023 - 1

walk the cat - 1/4/2023 - NULL

walk the dog - 1/5/2023 - 2