Abstract reference to an attribute of the table


#1

Hello,

I would like to know, if it is possible to use an “abstract reference” to a specific attribute for the calculation of the throughput times.

image .

Lets say my event log looks like on the picture and I am interested in calculating of the average throughput time for the Fall_ref values. Each case ID can have multiple Fall_ref’s, so the formula with specific Fall_refs makes no sence.

For example I would need to calculate:
(troughput(from A to B for Fall_ref=1111)+ troughput(from A to B for Fall_ref=1112)+troughput(from A to B for Fall_ref=2255)+troughput(from A to B for Fall_ref=2794))/4

What i want is
calculate AVG(throughput(A to B belonging to the same Fall_ref)).

Is it possible?

Thank you!


#3

I am not aware of the easy way. The complex way will be to create additional table with list of all Fall_refs and link it to event log. After that you can use pu_first(“fall_refs_table”, event time) and pu_last to calculate throughput


#4

Thank you, I think this solution will be optimal in our case!


#8

Dear DrWindy,

What is the use case behind your question?
Thanks!

Have fun!
Hans.


#9

We want to track the processing times (throughput times) for the correspondence-with-client documents over all contracts we have:)


#10

Hi, we found a partial solution to the problem with the help of the TARGET/SOURCE function. So we used the following filters:

FILTER SOURCE("ACTIVITIES"."Fall_Ref") = TARGET("ACTIVITIES"."Fall_Ref");

FILTER SOURCE(“ACTIVITIES”.“ACTIVITY_EN”) = ‘A’;

FILTER TARGET(“ACTIVITIES”.“ACTIVITY_EN”) = ‘B’;

The KPI would be

TARGET(REMAP_TIMESTAMPS("_CEL_AP_ACTIVITIES"."EVENTTIME", DAYS)) - SOURCE(REMAP_TIMESTAMPS("_CEL_AP_ACTIVITIES"."EVENTTIME", DAYS))

However, we have a roblem that some Fall_Refs disappear though they flow through both activities ‘A’ and ‘B’. The next problem, is that we what to know, which Fall_refs have no activity ‘B’ and how long it has been since the activity ‘A’ in this case.
Any ideas??

Best,
Maria


#11

Update:
the disappearing Fall_refs were the result of the eventsequence, i.e. if we have an Case with 3 File_refs (FL1, FL2, FL3) in the following sequence:
A(FL1)-B(FL1)-A(FL2)-A(FL3)-B(FL3)-B(FL2) then we will find only FL1 and FL3 in our table, as activities A and B for these Fall_refs directly follow each other. So we used the fact that the activities A and B can occur only once per Fall_ref and applied the following for the OLAP-Table
filter
FILTER “ACTIVITIES”.“ACTIVITY_DE” IN (‘A’, ‘B’)
dimensions
“ACTIVITIES”.“Case_ID”
“ACTIVITIES”.“Fall_ref”
KPI
MAX(REMAP_TIMESTAMPS(“ACTIVITIES”.“EVENTTIME”, DAYS)) - MIN(REMAP_TIMESTAMPS(“ACTIVITIES”.“EVENTTIME”, DAYS))

If only event A or B takes place for some CASE_ID, the value of the KPI is 0 (A and B can’t occur on the same day)

Best,
Maria

P.S. we r grateful to CELONIS Support Team for the idea


#12

Hello,

I have a similar issue as well,but I want to do this calculation on a Single KPI

If I remove dimentions, it’s not correctly calculate anymore. Is there a way to solve it ?


#14

The Problem in our case is to track the DOCIDs…if u remove the DOCID-Dimension you stop referencing single DOCIDs :frowning: I dont have any solution to that unfortunatelly. Would be, however, interested in it.

Good luck!
Maria


#15

Hi,
If I understand you correctly, you want to remove the two dimensions and the cycle time should be averaged? In Celonis 4.5 you will be able to use Domain_Table Pull Up functions, where you could use something like

AVG(
PU_MAX(DOMAIN_TABLE("ACTIVITIES"."CASE_KEY","ACTIVITIES"."Fall_Ref"), REMAP_TIMESTAMPS("ACTIVITIES"."EVENTTIME", days), "ACTIVITIES"."ACTIVITY_DE"
IN ('A' , 'B'))
-
PU_MIN(DOMAIN_TABLE("ACTIVITIES"."CASE_KEY","ACTIVITIES"."Fall_Ref"), REMAP_TIMESTAMPS("ACTIVITIES"."EVENTTIME", days), "ACTIVITIES"."ACTIVITY_DE"
IN ('A' , 'B'))
)

This would allow you to get the average of the throughput times of each Case_Key + Fall_Ref.
Best regards
Viana