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' ] ,

    d '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' ] ,

    i '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" ,

    2 'Create Sales Order Item' , 'Create Sales Order Item' ] ,

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

    d '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 t<Temporary table: SOURCE/TARGET configuration _CEL_O2C_ACTIVITIES,REMAP_VALUES ( "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" , b 'Create Sales Order Item' , 'Create Sales Order Item' ] , f 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] , r 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] , NULL ),ANY_OCCURRENCE ' ] TO ANY_OCCURRENCE ]>] and O<Temporary table: SOURCE/TARGET configuration _CEL_O2C_ACTIVITIES,REMAP_VALUES ( "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" , U 'Create Sales Order Item' , 'Create Sales Order Item' ] , f 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] , r 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] , t 'eCRD Pushed Out' , 'eCRD Pushed Out' ] , NULL ),ANY_OCCURRENCE [ ] TO ANY_OCCURRENCE t ]>] 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: ETemporary table: SOURCE/TARGET configuration _CEL_O2C_ACTIVITIES,REMAP_VALUES ( "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" , r 'Create Sales Order Item' , 'Create Sales Order Item' ] , f 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] , r 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] , NULL ),ANY_OCCURRENCE ' ] TO ANY_OCCURRENCE ]]N <-- 1!tVBAP]!1 --> NrTemporary table: SOURCE/TARGET configuration _CEL_O2C_ACTIVITIES,REMAP_VALUES ( "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" , l 'Create Sales Order Item' , 'Create Sales Order Item' ] , f 'Material Availability Date Pushed Out' , 'Material Availability Date Pushed Out' ] , r 'Customer Request Date Pushed Out' , 'Customer Request Date Pushed Out' ] , t 'eCRD Pushed Out' , 'eCRD Pushed Out' ] , NULL ),ANY_OCCURRENCE [ ] TO ANY_OCCURRENCE t ]]. 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