Skip to main content

Hello all, 

I’m currently working on combining various cycle times (e.g. from PO Item creation to Goods Receipt, or from Goods Receipt to Invoice Receipt) with key figures from my Case Table in a single OLAP table.

To achieve this, I’ve tested two different approaches:

  1. Cycle Time Calculation Based on the Activity Table: This method allows me to control which occurrence to consider — for example, in cases of multiple GRNs for the same PO line, I can choose to include only the first one. The formula delivers the expected results. However, I’m unable to combine this approach with other key figures from my Case Table (e.g. Company Code, PO Item Count, etc.) in the same OLAP table. Additionally, if the cycle time is negative (e.g. GRN before PO Item creation), the formula returns “–” instead of the actual negative value.

     

    AVG(CALC_THROUGHPUT (

    FIRST_OCCURRENCE [ 'Create Purchase Order Item']

    TO FIRST_OCCURRENCE [ 'Record Goods Receipt' ] ,

    REMAP_TIMESTAMPS ("_CEL_EKPO_ACTIVITIES"."EVENTTIME" , Days )

    ))

 

  1. Cycle Time Calculation Based on SAP Table Timestamps: This approach allows me to pull all relevant fields via BIND and PU to the Case Table level, enabling a unified OLAP table that includes all key figures. However, I cannot ensure that only the first occurrence is considered. In cases of multiple GRNs, the cycle time is calculated for each GRN and then averaged. I’ve also noticed that in some cases — e.g. when SAP shows 20 GRNs — the formula only recognizes 10 or 15, possibly due to limitations in the BIND and PU chain.


    AVG(DAYS_BETWEEN (BIND("BKPF",PU_FIRST("RBKP", BIND("RSEG","EKPO"."CREATIONDATE"))),

    BIND("BKPF",PU_FIRST("RBKP", BIND("RSEG", PU_FIRST("EKPO","MSEG"."BUDAT_MKPF"))))))

 

Does anyone have a solution or workaround for this scenario?
Any suggestions would be greatly appreciated.

Thanks in advance 🤜🤛

Hi Oli,

 

CALC_THROUGHPUT function only outputs positive value and if the value  is in negative it will return null, please refer to the below doc.

https://docs.celonis.com/en/calc_throughput.html#UUID-b90444ec-f857-a2b2-3de2-fd49074c0dac

You can consider using DATEDIFF with time units, time_unit: ms | ss | mi | hh | dd | mm | yy

https://docs.celonis.com/en/datediff.html#UUID-97eeedea-bff1-833b-71e8-ceb5331d0688

I hope this helps.

 

BR

Suveesh M

Thanks, Suveesh, for your response — much appreciated!
Unfortunately, I don’t see a way to use DATEDIFF in combination with identifying the first occurrence. In our case, it’s quite common to have multiple Goods Receipts for a single PO line item, which complicates things.

Thanks, Suveesh, for your response — much appreciated!
Unfortunately, I don’t see a way to use DATEDIFF in combination with identifying the first occurrence. In our case, it’s quite common to have multiple Goods Receipts for a single PO line item, which complicates things.

Hi Oli, you could combine DATEDIFF with a PU_FIRST / PU_LAST function depending on your preference. 

A screenshot from the documentation: 

 

For your case it would look something like this:

DATEDIFF ( 
hh , 
PU_FIRST ( "CASES" , "ACTIVITIES"."eventtime" , "ACTIVITIES"."ACTIVITY_EN" LIKE 'Create PO Item' ) , 
PU_FIRST ( "CASES" , "ACTIVITIES"."eventtime" , "ACTIVITIES"."ACTIVITY_EN" LIKE 'Record Goods Receipt' ) 
)

Please let me know whether this worked! 

 

Cheers

Denis 

 

Thanks Denis! This approach effectively resolves the issue with negative values when working with the Activity Table

Community Code of ConductAccessibility statement