Hi,

you first need to "mark" all "Send PO" Activities which are possible candidates, i.e. which come after a "Change Price", and not after a "Send PO". You can do this with ACTIVITY_LAG:

ACTIVITY_LAG( CASE WHEN "Activities"."Activity" IN ( 'Change Price', 'Send PO' ) THEN "Activities"."Activity" END )

When the result of this is "Change Price", the current row is a possible candidate, if it's "Send PO", it's not. We want the last candidate, and the candidate should itself be a "Send PO", so we can formulate the following PU_LAST:

PU_LAST("Activities_CASES", "Activities"."timestamp", "Activities"."Activity" = 'Send PO' AND ACTIVITY_LAG( CASE WHEN "Activities"."Activity" IN ( 'Change Price', 'Send PO' ) THEN "Activities"."Activity" END ) IN ( 'Change Price' ) )

Unfortunately this would not take the second case into account, where there is no "Change Price" followed by a "Send PO". In this case, we return the last (or first? You need to specify this case as well) "Send PO":

COALESCE (

PU_LAST("Activities_CASES", "Activities"."timestamp", "Activities"."Activity" = 'Send PO' AND ACTIVITY_LAG( CASE WHEN "Activities"."Activity" IN ( 'Change Price', 'Send PO' ) THEN "Activities"."Activity" END ) IN ( 'Change Price' ) ) ,

PU_LAST("Activities_CASES", "Activities"."timestamp", "Activities"."Activity" = 'Send PO' )

)

This should give you the timestamp of the "Send PO" you want. Now you need to put this in the first argument of your DAYS_BETWEEN and it should work fine.

Best

David

Hi @david.beche12,

thanks a lot for the quick reply!

I understand your solution and I like the distinction with COALESECE() to basically route depending on whether change prices occur or not.

One follow-up question:

AFAIK ACTIVITY_LAG() only checks the direct predecessor activity, correct?

What if we have a fourth variant with arbitrary other activities between the last Change Price activity and the Send PO activity of interest for the cycle time calculation, such as:

4.Send PO --> Change Price --> Send PO --> Change Price --> "arbitrary other activity(ies)" --> Send PO --> --> Send PO --> Receive Vendor Confirmation

In order words: the Send PO activity of interest does not necessarily have to occur DIRECTLY after the last Change Price activity but EVENTUALLY after it (with arbitrary other activities in between but no intermediate activities Change Price or Receive Vendor Confirmation).

Here we need something else than ACTIVITY_LAG to define the Send PO activity of interest?

Best,

Florian

Hi Florian,

it should be fine, because ACTIVITY_LAG skips NULL values, i.e. it returns the direct predecessor which is not NULL. With the CASE WHEN inside the ACTIVITY_LAG, I set all other activities to NULL, so they should not influence anything.

Best

David

HI @david.beche12,

you are right - Indeed your proposed statement already considers process variant 4. I have checked specific cases in my process and it works fine.

Once again, thanks ALOT for your help on this case.

I highly appreciate it,

best and good weekend,

Florian