Hi Maria, in order to calculate the throughput time between consecutive activities using SOURCE and TARGET, you can do the following: Dim1: SOURCE("activities"."activity") Dim2: TARGET("activities"."activity") KPI: HOURS_BETWEEN ( SOURCE("activities"."timestamp"), TARGET("activities"."timestamp") ) You can also find examples in the SOURCE / TARGET documentation. Cheers David

---

Hi Marina,

 

here is a great resource that explains calculations on activity sequences. Check out this documentation which also provides further scenarios + solutions:

https://confluence.celonis.com/display/PQLdevelopment/Calculations+on+Activity+Sequences

 

Best,

Kevin

---

Hello Maria,

Better than SOURCE() and TARGET() is to use ACTIVITY_LAG

ACTIVITY_LAG ( table.column [, offset ] )

You could simply use DAYS_BETWEEN,ACTIVITY.ACTIVITY_EN,ACTIVITY_LAG(ACTIVITY.ACTIVITY_EN,1))

Best,

Gabriel

---

Hi Gabriel, 

I have created 2 variables

1.crd_mad_pushout: case when SOURCE (

    "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ,

   REMAP_VALUES (

    "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ,

    [ 'Create Sales Order Item' , 'Create Sales Order Item' ] ,

    [ 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] ,

    [ 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] ,

    NULL

   )

   )='Customer Request Date Pushed Out' and target("_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ) = 'Material Availability Date Pushed Out'

then   round(datediff(dd,

SOURCE (

    "_CEL_O2C_ACTIVITIES"."EVENTTIME" ,

   REMAP_VALUES (

    "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ,

    [ 'Create Sales Order Item' , 'Create Sales Order Item' ] ,

    [ 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] ,

    [ 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] ,

    NULL

   )

   )

,

target("_CEL_O2C_ACTIVITIES"."EVENTTIME"))) 

when target("_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ) = 'Material Availability Date Pushed Out'

then 9999

ELSE NULL END

2.ecrd_mad_pushout:

case when SOURCE (

    "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ,

   REMAP_VALUES (

    "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ,

    [ 'Create Sales Order Item' , 'Create Sales Order Item' ] ,

    [ 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] ,

    [ 'eCRD Pushed Out' , 'eCRD Pushed Out' ] ,

    NULL

   )

   )='eCRD Pushed Out' and target("_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ) = 'Material Availability Date Pushed Out'

then   round(datediff(dd,

SOURCE (

    "_CEL_O2C_ACTIVITIES"."EVENTTIME" ,

   REMAP_VALUES (

    "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ,

    [ 'Create Sales Order Item' , 'Create Sales Order Item' ] ,

    [ 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] ,

    [ 'eCRD Pushed Out' , 'eCRD Pushed Out' ] ,

    NULL

   )

   )

,

target("_CEL_O2C_ACTIVITIES"."EVENTTIME"))) 

when target("_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" ) = 'Material Availability Date Pushed Out'

then 9999

ELSE NULL END

 

which calculates the date difference between 2 set of activities
when i am trying to keep filter using these two variables, getting error

“No common table could be found. The tables [<Temporary table: SOURCE/TARGET configuration _CEL_O2C_ACTIVITIES,REMAP_VALUES ( "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" , [ 'Create Sales Order Item' , 'Create Sales Order Item' ] , [ 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] , [ 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] , NULL ),ANY_OCCURRENCE [ ] TO ANY_OCCURRENCE [ ]>] and [<Temporary table: SOURCE/TARGET configuration _CEL_O2C_ACTIVITIES,REMAP_VALUES ( "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" , [ 'Create Sales Order Item' , 'Create Sales Order Item' ] , [ 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] , [ 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] , [ 'eCRD Pushed Out' , 'eCRD Pushed Out' ] , NULL ),ANY_OCCURRENCE [ ] TO ANY_OCCURRENCE [ ]>] are connected, but have no common table. This means that they do not have a direct (or indirect) 1:N or N:1 relationship. Join path: [Temporary table: SOURCE/TARGET configuration _CEL_O2C_ACTIVITIES,REMAP_VALUES ( "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" , [ 'Create Sales Order Item' , 'Create Sales Order Item' ] , [ 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] , [ 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] , NULL ),ANY_OCCURRENCE [ ] TO ANY_OCCURRENCE [ ]]N <-- 1![VBAP]!1 --> N[Temporary table: SOURCE/TARGET configuration _CEL_O2C_ACTIVITIES,REMAP_VALUES ( "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" , [ 'Create Sales Order Item' , 'Create Sales Order Item' ] , [ 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] , [ 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] , [ 'eCRD Pushed Out' , 'eCRD Pushed Out' ] , NULL ),ANY_OCCURRENCE [ ] TO ANY_OCCURRENCE [ ]]. For more information on the join path, search for "Join functionality" in PQL documentation”


Can you help me with this

 

---

Can you please help me with the above one ​@gabriel.okaba11 

---