Skip to main content
Question

Identify cases with postings in different periods

  • June 24, 2020
  • 5 replies
  • 4 views
C
Level 1

Hi,

Im 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 Im 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 doesnt necessarily equals the posting period Ive 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

5 replies

S
Level 3
  • sion.oppen
  • Level 3
  • June 26, 2020

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

C
Level 1
Hi, thanks a lot for your proposed solution, its 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
S
Level 3
  • sion.oppen
  • Level 3
  • July 1, 2020
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?
C
Level 1

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

S
Level 3
  • sion.oppen
  • Level 3
  • July 1, 2020

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

Community Code of ConductAccessibility statement