Throughput time from one event to the last from three


#1

Hello,

I am interested in calculating the throughput time from Activity1 to one of the three activitities (whichever occured as first/last after) as a KPI. Could you please help with the code statement for that?

Thank you!


#3

Hello Maria,

I believe a function like:

HOURS_BETWEEN(PU_FIRST(VBAP,"_CEL_O2C_ACTIVITIES".“EVENTTIME”, “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” = ‘Create Sales Order Item’), PU_FIRST(VBAP, “_CEL_O2C_ACTIVITIES”.“EVENTTIME”, “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” IN (‘Create Delivery’,‘Goods Issue’,‘Create Invoice’)))

Should work.

Best,
Henry


#4

I’ve just tried

HOURS_BETWEEN(PU_FIRST(“CEL_A_CASES”,“CEL_A_ACTIVITIES”.“ACTIVITY_DE”=‘Activity1’), PU_FIRST(“CEL_A_CASES”,“CEL_A_ACTIVITIES”.“ACTIVITY_DE” IN (‘Activity2’, ‘Activity3’, ‘Activity4’, ‘Activity5’, ‘Activity5’)))

and it shows me the following error:
image

Thank you


#5

Hello Maria,

the correct syntax for the pull function is:

PU_FIRST(Table, Column, Condition)

You forgot to specify the column. Let me know if it works after you specified the eventtime column from the activity table.

Best,
Henry


#6

Thank you, that worked:)


#7

I have a further question to this topic. In my case, the Activities 2,3 and 4 can also appear before activity 1 (the values are then negative). i am, however, interested only in the situation where the Activities 2,3 or 4 took place AFTER the Activity 1. How can I modify the statement to account for that?:slightly_smiling_face:

Thank you!


#8

Hello Maria,

you have several options to do this.

Firstly, you could use an Analysis/Sheet/Component Filter limiting to the cases where activity 2,3,4 do not occur before actvity 1.

Alternatively, you could calculate the ratio using a case when statement with the logic “AVG(CASE WHEN activity 2,3,4 do not occur ahead of activity 1 then HOURS_BETWEEN(…) ELSE NULL END)”.

Let me know if there are any issues with the implementation. The visual KPI editor might be helpful for creating the activity order logic.

Best,
Henry


#9

Can you please give me an example of this filter? Would be very useful. Thank you!


#10

Hallo Maria,

Here is the PQL code for both options. You need to insert the activity and table names in the indicated place.

Option 1 FILTER:

FILTER PROCESS NOT EQUALS ‘Activity 2’ TO ANY TO ‘Activity 1’ END;

FILTER PROCESS NOT EQUALS ‘Activity 3’ TO ANY TO ‘Activity 1’ END;

FILTER PROCESS NOT EQUALS ‘Activity 4’ TO ANY TO ‘Activity 1’ END;

Option 2 Use Case When:

AVG(

CASE WHEN PROCESS NOT EQUALS ‘Activity 2’ TO ANY TO ‘Activity 1’ AND

PROCESS NOT EQUALS ‘Activity 3’ TO ANY TO ‘Activity 1’ AND

PROCESS NOT EQUALS ‘Activity 4’ TO ANY TO ‘Activity 1’

THEN

HOURS_BETWEEN(PU_FIRST(“YOUR TABLE”,“ACTIVITY TABLE”.“EVENTTIME”, “ACTIVITY TABLE ".“ACTIVITY_EN” = ‘Activity 1’),

PU_FIRST("YOUR TABLE"," ACTIVITY TABLE "."EVENTTIME", "ACTIVITY TABLE "."ACTIVITY_EN"

IN

(‘Activity 2’,‘Activity 3’,‘Activity 4’)))

ELSE NULL

END)

Best


#11

Activities 2-4 can happen both before and after the Activity 1. If I use your filter I completely throw away the cases where activities 2-4 happened before Activity 1 even if they also happened after.