Identify cases with postings in different periods

Hi,
I’m searching for a solution (coding statement example) to identify O2C cases where the same activity was executed multiple times but in different posting periods, e.g. in a third party sales the posting of the statistical goods receipts.
The cases where the activity I’m interested in was executed multiple times can easily identified viay the process flow selection: activity “Post PGR” is anytime followed by “Post PGR”, but how to achieve a single row which identfies the orders where this activity happened for different posting periods. As the timestamp (e.g the month) of the activity doesn’t necessarily equals the posting period I’ve already added the posting period to the activity table. Does anyone can help me with this, ideally I want to show in the analysis the first or last posting period together with the case key ?
Cheers
Chris

Hi Chris, Perhaps this can be achieved by validating whether the date difference in months between the first and last ‘Post PGR’ activity is exceeds 1? For example:

CASE WHEN DATEDIFF(MM, PU_FIRST(“Sales Table”, “Activity Table”.“Event Time”, “Activity Table”.“Activity” = ‘Post PGR’), PU_LAST(“Sales Table”, “Activity Table”.“Event Time”, “Activity Table”.“Activity” = ‘Post PGR’) > 1 THEN ‘Cross-Period PGR’ ELSE ‘Same Period PGR’ END

Hi, thanks a lot for your proposed solution, it’s improving my analysis, but the tricky part is that I do have an eventtime in different months, but for the same period. The issue is that I have the posting periods in different activities ? Any further idea ? Cheers Chris

Hi Chris, Do I understand correctly the ‘Post PGR’ has an event time in the Activity Table equal to the Entry Date of the PGR, but you want to validate the actual Posting Date of the PGR (which may be backdated, and therefore in same period while Entry Date is not)? If so, do you have the table for the PGR in Celonis from where you could pull the Posting Date?

Hi,
mc situation is as follows
Example 1:
For Order 15001 item 0010 I do have 2 activities named Post PGR, one with an eventtime(in the activity table) in May, one with an eventtime in JUne, but both are posted in period May (the second one backdetd)
Example 2:
For Order 15099 item 0010 I do have 2 activities named Post PGR, one with an eventtime in May, one with an evnttime in JUne, but the first GR is posted in period May whereas the second GR is posted for period June.

I added the posting period via table MPKPF to the activity table. I do have extracted MKPF to Celonis but MKPF is not in the O2C data model.

_CASE_KEY Month(Timestamp First value per case(Timestamp) Last value per case(Timestamp) Posting Period Count(Activity (German))
15001000010 5 Mai 29 2020 09:53 PM Jun 8 2020 09:55 PM 5 1
15001000010 6 Mai 29 2020 09:53 PM Jun 8 2020 09:55 PM 5 1
15099000010 5 Mai 29 2020 09:55 PM Jun 22 2020 09:48 PM 5 1
15099000010 6 Mai 29 2020 09:55 PM Jun 22 2020 09:48 PM 6 1

In my analysis I only wanted to select those cases like example 2

Hi Chris, In that case you want to compare the Posting Period values. Since this only refers to the month and not the year, you may want to apply two criteria:

  1. First and last activity were in the same posting period (month)
  2. First and last activity were posted within 1 month (to differentiate between years)

CASE WHEN
DATEDIFF ( MM, PU_FIRST ( “Sales Table” , “Activity Table”.“Event Time” , “Activity Table”.“Activity” = ‘Post PGR’ ) , PU_LAST ( “Sales Table” , “Activity Table”.“Event Time” , “Activity Table”.“Activity” = ‘Post PGR’ ) <= 1 AND
PU_FIRST ( “Sales Table” , “Activity Table”.“Posting Period” , “Activity Table”.“Activity” = ‘Post PGR’ ) = PU_LAST ( “Sales Table” , “Activity Table”.“Posting Period” , “Activity Table”.“Activity” = ‘Post PGR’ )
THEN ‘Same Period PGR’
ELSE ‘Cross-Period Period PGR’
END