Count cases with condition

Dear Alexan, thank you for reaching out via the Celonis Community Forum. I am going to answer your question in a short and a long, more elaborated, way: Short: Your query works exactly as you want it to work. Long: The core of your query is the following statement: CALC_CROP(FIRST_OCCURRENCE[MY_EVENTS1] TO LAST_OCCURRENCE[MY_EVENTS2], CASE WHEN DB_EVENTS.EVENT_NAME LIKE Event_1% THEN MY_EVENTS1 WHEN DB_EVENTS.EVENT_NAME LIKE Event_2% THEN MY_EVENTS2 ELSE DB_EVENTS.EVENT_NAME END) With this statement you crop the process and the only the steps from MY_EVENTS1 to MY_EVENTS2 remain. (e.g. A-B-MY_EVENTS1-C-MY_EVENTS2-D-E gets cropped to MY_EVENTS1-C-MY_EVENTS2) So far so good. The surrounding PQL statement is: SOURCE (DB_EVENTS.EVENT_SDTS, //CROP FIRST_OCCURRENCE TO LAST_OCCURRENCE), TARGET(DB_EVENTS.EVENT_SDTS) Two important remarks here are, that on the one side, it is totally fine, that the specifier FIRST TO LAST doesnt appear in the TARGET statement, as it gets inheritet and on the other side the specifier FIRST TO LAST is used s you can see in this model: By using this specifier you only consider the the first and last activity for your source target operation. With regards to the remaining process flow above that perfectly works as we skip activity C or any further activities in between. The surrounding PQL statement are AVG( DAYS_BETWEEN( //SOURCE TARGET //CROP )) which calculate the days_between for all cases and take the average. Attention: Bear in mind that this formula does not take cases into account that show the starting activity MY_EVENTS1 but have not yet reached the target activity MY_EVENTS2.

Many thanks!