Count cases with condition

Dear Team.

I need to calculate count of cases with condition.
Condition is, when case appeared in the period from 0 to 10 days. I gave an example of the calculation below, but unfortunately I can’t be sure that it is correct. Please, read and give a feedback.

CASE WHEN

AVG (DAYS_BETWEEN(SOURCE (“DB_EVENTS”.“EVENT_SDTS”, 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),
FIRST_OCCURRENCE TO LAST_OCCURRENCE
),

TARGET(“DB_EVENTS”.“EVENT_SDTS”)
))>0
AND
AVG (DAYS_BETWEEN(SOURCE (“DB_EVENTS”.“EVENT_SDTS”, 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),
FIRST_OCCURRENCE TO LAST_OCCURRENCE
),
TARGET(“DB_EVENTS”.“EVENT_SDTS”)
))<10

THEN COUNT_TABLE(“DB_CASES”) ELSE NULL END

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 doesn’t 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:

image

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’.

1 Like

Many thanks! :smiley: