Hi Manoah,

thank you for your question.

You can solve that task using our ACTIVITY_LEAD function, which will return the next activity for each activity.

So you could do this:

This should filter on all set price block activities which are followed by change price and remove price block. In your actual query, you can then simply count the activities for each case. Alternatively, you can also use the above condition inside a CASE WHEN.

Your use case is pretty similar to the Ping Pong Cases use case, in particular the Direct Ping Pong Cases. You can find this as an example in our Examples and Use Cases section in the documentation.

Hope this helps!

Cheers

David

Hi David,

really appreciate your support. Thanks!

If I got it right, with the ACTIVITY_LEAD function I can only count the occurances of one defined process pattern e.g.:

set price block - change price - remove price block

Right?

Probably my description of the use case wasnt accurate enough. Sorry for that.

Between those activities there can be several other. It is only important, that the sequence is correct:

set price block - - change price - - remove price block

A price block can be removed at any time in the process. What I am interested in is, if there has been a price change before.

I hope this made my request somehow clearer.

Thanks again.

Manoah

Hi Manoah,

Any luck on the above problem?

I too have a similar problem like this. Can you help David?

Hi all,

you can skip activities inside the ACTIVITY_LEAD or ACTIVITY_LAG by setting them to NULL.

Since you formulated the question from the perspective of the remove price block activity, I would suggest to use ACTIVITY_LAG. Basically we need to:

• Look at each "remove price block"
• see if there is a change price before (skip all other activities except "set price block")
• see if there is a "set price block" before that (skip all other activities including "change price", but do not skip "remove price block" to catch cases where you have a "remove price block", but no corresponding "set price block" ).

So the formula would be this:

FILTER ACTIVITY_COLUMN() = 'remove price block'

AND ACTIVITY_LAG( CASE WHEN ACTIVITY_COLUMN() IN ('set price block', 'change price') THEN ACTIVITY_COLUMN() ELSE NULL END ) = 'change price'

AND ACTIVITY_LAG( CASE WHEN ACTIVITY_COLUMN() IN ('set price block', 'remove price block') THEN ACTIVITY_COLUMN() ELSE NULL END ) = 'set price block';

Hope this helps.

Hi David,

Appreciate your help. Can you elaborate a bit more on how to use it in an activity lead function. My problem requires me to look at 'non directly follows' patterns multiple times. For example, this is my current code

Case When

"ACTIVITIES"."ACTIVITY"='A' 

AND ACTIVITY_LEAD ("ACTIVITIES"."ACTIVITY" )!='C'

 THEN 'C is not found' else 'else C is found' END

I want to look for how many such patterns occur in my case  However, in the code i am using I can only look at directly follows, how can i modify this code to make it look for non directly follows. So that If an activity occurs between A and C i should be able to ignore it. Thanks.

Hi @anshuman.kher 

You would need to make sure that all activities (except activity C) are skipped inside the ACTIVITY_LEAD.

So something like this should work:

Case When

"ACTIVITIES"."ACTIVITY"='A' 

AND ACTIVITY_LEAD ( CASE WHEN "ACTIVITIES"."ACTIVITY" = 'C' THEN "ACTIVITIES"."ACTIVITY" ELSE NULL END ) IS NULL

 THEN 'C is not found' else 'else C is found' END

This ACTIVITY_LEAD basically says: "Return the next C for every row". When is C is found it will return the C. If no C is found, it returns NULL (which is why I compare the ACTIVITY_LEAD result to NULL in the CASE WHEN condition).

Best

David

Hi David,

Appreciate the quick response:-). This has been a big help, we still require a little bit more inputs to take us over the finish line. As the problem we are trying to resolve requires multiple conditions within.

As per your advise we have written the below code

CASE

WHEN ""ACTIVITIES"."ACTIVITY"='A'

AND ACTIVITY_LEAD (CASE WHEN ""ACTIVITIES"."ACTIVITY" = 'C'

THEN "ACTIVITIES"."ACTIVITY" ELSE NULL END) IS NULL

THEN 'Step C is Missing'

WHEN ""ACTIVITIES"."ACTIVITY"='C''

AND ACTIVITY_LEAD (CASE WHEN "ACTIVITIES"."ACTIVITY = 'D'

THEN "ACTIVITIES"."ACTIVITY ELSE NULL END) IS NULL

THEN 'D is Missing

WHEN "ACTIVITIES"."ACTIVITY ='D'

AND ACTIVITY_LEAD (CASE WHEN "ACTIVITIES"."ACTIVITY" = 'E'

THEN "ACTIVITIES"."ACTIVITY ELSE NULL END) IS NULL

THEN 'E is missing''

Else '0' END

What happens when we run the code is that the previous conditions are overwritten. Example, A-C-D-E is our ideal path. But we can have other sub activities in between like x-y-z.

When the code evaluates the third condition D-E, we also need to maintain the results of the conditions evaluated earlier, i.e. A-C and C-D need to be present in that path at a case level. So that in a single view, we can see all the failure points in a case.

So if a case has an ideal path as A-C-D-E and it has gone through only D-E, we need to be able to show all the failed instances, i.e. A-C-failed and C-D failed as well. Therefore, in this case, we will see two instances of the case where A-C has failed and C-D has failed.

Hi @david.beche12 

I just wanted to checked if you had a resolution to my question

Thanks.

Hi @anshuman.kher 

This is a bit of a different problem. In that case you would have to "generate" rows for each condition that is fulfilled. What could help you here is our brand new MULTI CASE WHEN functionality: https://docs.celonis.com/en/multi-case-when.html. This function returns a row for every fulfilled condition, not just for the first one.

So you could write the MULTI keyword in front of your CASE WHEN statement. But maybe that is not enough for your use case. Because if I understand correctly you would also like to get the "A->C" violation also when "A" is not present at all. But then the question is; should it also mark "A->C" as failed when it is in the case, but not at the right position (e.g.., in case D->A->C->E)?