Using "LIKE" in Process Functions as CALC_THROUGHPUT

Dear Colleagues,

I have a problem: I am calculating days with CALC_THROUGHPUT function. For some events I need to use “LIKE” function. But As I see, it is not possible to use one, only in func. FILTER or CASE WHEN. But How to apply event in CALC_THROUGHPUT when you need to cut a part of name?
For example.
AVG(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Event1’]
TO LAST_OCCURRENCE[‘Event2:%’],
REMAP_TIMESTAMPS(“EVENTS_EVENT”.“EVENT_DATE”, DAYS)))

Or maybe it can be done by another function of Celonis.

Thanks

Hi Alexan,

welcome to the Celonis Community!

If you are on the IBC or CPM4.6, you can pass a column as a third argument of REMAP_TIMESTAMPS. There you can use a CASE WHEN to map all events that should be considered to the same string.

AVG(CALC_THROUGHPUT(FIRST_OCCURRENCE['Event1']
TO LAST_OCCURRENCE['MY_EVENTS'],
REMAP_TIMESTAMPS("EVENTS_EVENT"."EVENT_DATE", DAYS),
CASE WHEN "EVENTS_EVENT"."EVENT" LIKE 'Event2:%' THEN 'MY_EVENTS' ELSE "EVENTS_EVENT"."EVENT" END
))

In this query, all events that are LIKE 'Event2:%' are mapped to ‘MY_EVENTS’ (you can use another name which describes the group of activities better of course). You can then use this in the LAST_OCCURRENCE input.

Best
David

1 Like

Hi d.becher.
Thanks for the response.

Pass a column in data base or in Celonis as a third argument? I am not sure that I can use a third argument column in Celonis (not DB) in calculation.

Which celonis version are you using?

I am using 4.5. Is it a problem to solve my subject on this version of Celonis?

On CPM4.5, you indeed cannot pass the additional column to the CALC_THROUGHPUT.

However you can use SOURCE/TARGET and CALC_CROP, but the basic idea is the same:

AVG(
DAYS_BETWEEN(
SOURCE ( "EVENTS_EVENT"."EVENT_DATE",
CALC_CROP(FIRST_OCCURRENCE['Event1'] TO LAST_OCCURRENCE['MY_EVENTS'],
CASE WHEN "EVENTS_EVENT"."EVENT" LIKE 'Event2:%' THEN 'MY_EVENTS' ELSE "EVENTS_EVENT"."EVENT" END),
FIRST_OCCURRENCE[] TO LAST_OCCURRENCE[]
),
TARGET("EVENTS_EVENT"."EVENT_DATE")
)
)

This should also work in CPM4.5.

Best
David

1 Like

Hi,
This additional column ‘MY_EVENTS’ were taken from where? Was it made in DB or in OLAP Table in celonis dashboard?

Thanks

Hi,

MY_EVENTS is not a column, it is just a string value that you assign to all the Events that fulfill your LIKE condition.

Example: In the Activity column of your Activity table, you have those Activities:

AA
AB
CD
AD

Now you map all Activities that are LIKE 'A%' to ‘MY_EVENTS’. The values now look like this:

MY_EVENTS
MY_EVENTS
CD
MY_EVENTS

This column is now used inside the CALC_CROP, so you can use LAST_OCCURRENCE[‘MY_EVENTS’] to refer to the last activity that was mapped to MY_EVENTS. Instead of MY_EVENTS, you can use a different string of course.

Best
David

1 Like

Thanks. It works perfectly.