I’d like to offer the opportunity to select purchase orders of a time periode where the selection criteria is the date of activity “goods receipt”. As long as the selected time periode matches the first or last activity “goods receipt” I can use the PU_ formulas but I’ve no solution for any goods receipt.
As example (GR for goods receipt)
August: GR order A; GR order B
September : GR order B
October:GR order A; GR order B
selecting August with PU_FIRST(“EKPO”, “_CEL_P2P_ACTIVITIES”.“EVENTTIME”, “_CEL_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘Goods receipt’) ==> result order A and B
similar selection October with PU_LAST instead of PU_FIRST
but how to find order B in September as it is neither first nor last?
I think I’ve found a way to get what you’re looking for. I used the following method in an OLAP table on Celonis 4.3:
- Create the following custom dimension
CASE WHEN _CEL_P2P_ACTIVITIES.ACTIVITY_EN = ‘Goods Receipt’ THEN ROUND_MONTH("_CEL_P2P_ACTIVITIES".“EVENTTIME”) ELSE NULL END
This will give you the Month of ANY/ALL of your GR Activities.
Add a KPI of Case Count. If you want Order header level count, use COUNT_TABLE(EKKO). This will tell you how many PO Lines or PO’s have a GR in that month.
Apply a component filter to the table to get rid of all the non-GR activities populating the “ELSE” condition of the dimension’s case statement.
FILTER _CEL_P2P_ACTIVITIES.ACTIVITY_EN = ‘Goods Receipt’
I realize this isn’t quite as elegant as a single KPI formula, but it get’s the job done. Hope it helps!
thank you that solved my issue - I tried something similar but didn’t thought about the component filter to get rid of the NULL lines.