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
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")
)
)
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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.