Skip to main content

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

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

Thanks. It works perfectly.
Community Code of ConductAccessibility statement