Hi all,
I would like to combine the MERGE_EVENTLOG() and PULL_UP functions, but am not sure how to do this. See the full problem description below.
We are looking at a use case where we want to visualize the customer interactions during an order. We are here combining two case tables and two activity tables, using the Multi Event-Log (MEL) functionality:
Celonis Data Models-MEL Simplified (2)841231 6.7 KB
- The first Case and Activity table represent Events logged on a customer_id level (red)
- The second Case and Activity table represent events logged on an order_id level (orange)
We succeeded to create an OLAP table where we count the number of inbound interactions during a order, i.e. interaction timestamp falls between order_start and order_end. We therefore used this code:
SUM(
CASE
WHEN DATE_BETWEEN ( <%= MergedTimestamps %>,
PU_FIRST("order_case_table", "order_activity_table"."order_timestamp"),
PU_LAST("order_case_table", "order_activity_table"."order_timestamp") ) = 1
AND <%= MergedEvents %> LIKE 'Inbound Call'
THEN 1.0
ELSE 0.0
END
)where MergedTimestamps is a text replacement variable like:
MERGE_EVENTLOG("order_activity_table"."order_timestamp" , "cust_activity_table"."cust_timestamp")and MergedEvents is a text replacement variable like:
MERGE_EVENTLOG("order_activity_table"."order_activity_name" , "cust_activity_table"."cust_activity_name")This resulted in a OLAP table like below, with the stated script in the column at the right-hand side:
Customer ID
Order ID
Order Start
Order End
Inbound Calls During Order
CID_001
OID_001
01-01-18
01-01-18
0
CID_003
OID_002
01-01-18
02-01-18
1
CID_002
OID_003
01-01-18
01-01-18
0
CID_003
OID_004
02-01-18
02-01-18
0
CID_003
OID_005
02-01-18
22-01-18
2
CID_001
OID_006
31-03-18
30-04-18
1
So far, so good. Now we would like to implement this in a column chart, where the horizontal axis shows the number of calls during an order, and the vertical axis shows the number of cases that has that amount. Having the values of the table in mind, this would result in this graph:
The problem right now is that the stated formula only works in a OLAP with the formula as KPI, and the Order_ID as dimension. If we apply the formula in a column chart component with
COUNT_TABLE("order_case_table")as dimension, the all the orders and interactions are just summed, but not per order:
(Sorry for the drawed 4, this was the fastest way to use dummy data)
Long story short, I think well need to use Pull-up functions in the formula to make it stand-alone without the order_ids as dimension. However, it is unclear to me how to combine the MEL-functions together with the PULL_UP functions.
I would love to hear your suggestions. Other interesting related use cases with the MERGE_EVENTLOG operator are also welcome!
Bests,
Jan-peter