Time difference Problem

Hello everybody,

I need your help.

I have to calculate the throughput time of my requisition.

In Table 1 (EBAN) I find the start date (“Lieferdatum”).
Since a requisition can have multiple positions, I need the earliest date (PU_FIRST?).

In Table 2 (Actiontable) I find the end date.
Here I need the “Bestellnummer” activity for the purchase requisition and the last date of each Bestellnummer (PU_LAST?).

I hope someone can help.

The photo serves as an example.

greetings

Hi Steven,

There are multiple solutions to calculate the throughput times. The first solution calculates the throughput time between events and is explained here: https://help.celonis.cloud/help/x/7IQeAg.
Having a look at your data (especially the ‘EBAN’ table), your proposed solution would be better. The formula then becomes:
DATEDIFF(dd, PU_FIRST(<parent table>, EBAN.Lieferdatum), PU_LAST(<parent table>, Actiontable.EVENTTIME, Actiontable.Anderungskennzeichen LIKE "Bestellnummer"))
You see that there is a need for a parent table, I expect that that will be the name of your case table in this case. Hopefully this does the trick.

Bests,
Jan-peter

Thank you very much.
Unfortunately, it still doesn’t work properly.
You are right. My Case Table and so my parent table is EBAN.
It seems that the Problem is the PU_FIRST statement.

Here my real formula:

DATEDIFF(dd, PU_FIRST(“EBAN”,“EBAN”.“FRGDT”), PU_LAST(“EBAN”,“T_CEL_AT_HP3”.“EVENTTIME”,“T_CEL_AT_HP3”.“Kurzbeschreibung” LIKE ‘Bestellnummer’))

If I separate the formulas I get an error for the first part PU_FIRST (“EBAN”, “EBAN”. “FRGDT”).

mhhhh…

Hi StevenB, The following approach may work for you. I was able to apply it in an invoice analysis using Invoice Header and Event Log. It is similar to the formula you shared, except that there should be no need for the PU_FIRST on EBAN.

DATEDIFF(DD, “EBAN”.“FRGDT”, PU_LAST(“EBAN”, “T_CEL_AT_HP3”.“EVENTTIME”, “T_CEL_AT_HP3”.“Kurzbeschreibung”=‘Bestellnummer’))

Hello. Thank you for your answer.

I think we need PU_FIRST here.
If you look at my screenshot above, then I have several start dates per BANF in the EBAN table. But I need the first one. In my example 01.01.2020

Hi Steven,

Is the BANF your unique identifier (or Case ID)? I think it would be a good practice to keep only unique values of your identifier in your Case Table. In that situation, I would suggest to create a new Case Table, with only the unique BANF entries. I think the Data job for that is:

DROP TABLE IF EXISTS <table_name>;

CREATE TABLE <table_name> (
	BANF	INTEGER);

INSERT INTO <table_name> (
    SELECT DISTINCT BANF
    FROM EBAN);

You can than connect the EBAN and Actiontable to this. Now the PU_First statements should work, where you pull up to the just created table.

Bests,
Jan-peter