Dealing with one Timestamp column - top of 5 activities by duration and rework

#1

Hi Guys,

I’m struggling with two visualization in Celonis. Problem is that I have only one Timestamp for Events (only when event starts). So one event for particular case end in time when the next event starts.

  1. First thing which I’m trying to do is to create column chart which show’s top 5 activities which have the longest duration. I was trying of different ways but nothing works, eg:
    AVG(CALC_THROUGHPUT(ALL_OCCURRENCE[“EventLog.csv”.“CreatedDate”] TO ALL_OCCURRENCE[“EventLog.csv”.“CreatedDate”], REMAP_TIMESTAMPS(“EventLog.csv”.“CreatedDate”, DAYS)))

  2. Second thing I’m trying to do (but I’m not sure it even possible to do it in Celonis) it is to calculate “rework”. By “rework” I mean:
    when for one case, particular activity occures twice, (for example the Mortgage Offer was Generated and after that was Mortgage Offer was Rejected and then Mortgage Offer was Generated again, the rework will be time between occurence of “Mortgage Offer Generated” events.

I was trying of different ways but nothing works:
AVG(DISTINCT CASE WHEN ACTIVATION_COUNT(“EventLog.csv”.“EventName”) > 1 THEN REMAP_TIMESTAMPS(“EventLog.csv”.“CreatedDate”, DAYS) ELSE 0 END))

Do you have some suggestions/ideas please?

Martyna

0 Likes

#2

Hi Martyna,

welcome to the Celonis community!

Both questions can be solved with the SOURCE/TARGET operator. This can be used to generate a temporary table where values of consecutive rows are stored in separate columns in the same row. You can find the documentation here https://help.celonis.de/pages/viewpage.action?pageId=17040434 (for cpm4) or here https://help.celonis.cloud/pages/viewpage.action?pageId=13566572 (for IBC).

So for the first question, you can calculate the time per activity using

TARGET ( REMAP_TIMESTAMPS ( "EventLog.csv"."CreatedDate", DAYS ) ) - SOURCE ( REMAP_TIMESTAMPS ( "EventLog.csv"."CreatedDate", DAYS ) )

To display the corresponding activity, add a second dimension to your OLAP table with

SOURCE("EventLog.csv"."EventName")

The second task can also be solved using this operator. We basically want to do the same thing as in the first question, but we only want to consider the Mortgage Offer was Generated activity. We can use REMAP_VALUES to map all activity names except this activity to NULL. This can then be used inside the SOURCE/TARGET filter expression (all values that are NULL will be ignored). If you are not on the IBC or Celonis 4.5, you need to use CASE WHEN instead of REMAP_VALUES.

TARGET ( REMAP_TIMESTAMPS ( "EventLog.csv"."CreatedDate", DAYS ), REMAP_VALUES("EventLog.csv"."EventName", ['Mortgage Offer was Generated', 'Mortgage Offer was Generated'], NULL) ) - SOURCE ( REMAP_TIMESTAMPS ("EventLog.csv"."CreatedDate", DAYS ) )

or with CASE WHEN:

TARGET ( REMAP_TIMESTAMPS ( "EventLog.csv"."CreatedDate", DAYS ), CASE WHEN "EventLog.csv"."EventName" = 'Mortgage Offer was Generated' THEN "EventLog.csv"."EventName" ELSE NULL END ) - SOURCE ( REMAP_TIMESTAMPS ("EventLog.csv"."CreatedDate", DAYS ) )

Again, if you want to display the corresponding Activity / Case, add a dimension containing

SOURCE("EventLog.csv"."EventName")

or

SOURCE("EventLog.csv"."Case")

Note that if one Case contains the Mortgage Offer was Generated Activity for example three times, the result will contain the throughput time between the first and second as well as the time between the second and third Activity.

Feel free to ask if there are any open questions!

David

1 Like