Skip to main content
Question

Time difference Problem

  • June 23, 2020
  • 5 replies
  • 0 views
steven.bernd
Level 6
Forum|alt.badge.img+1

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.

image1020472 24.9 KB

greetings

5 replies

janpeter.van.d
Level 12
Forum|alt.badge.img+26

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

steven.bernd
Level 6
Forum|alt.badge.img+1
  • steven.bernd
  • Author
  • Level 6
  • June 23, 2020

Thank you very much.

Unfortunately, it still doesnt 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).

image489619 8.89 KB

mhhhh

S
Level 3
  • sion.oppen
  • Level 3
  • June 24, 2020
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))
steven.bernd
Level 6
Forum|alt.badge.img+1
  • steven.bernd
  • Author
  • Level 6
  • June 24, 2020
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
janpeter.van.d
Level 12
Forum|alt.badge.img+26
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
Community Code of ConductAccessibility statement