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, 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
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:
- First and last activity were in the same posting period (month)
- 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
