Number of cases in which the same event occured more than once


#1

Hello everyone,

In light of a rework analysis im looking for a histogram showing the amount of cases in which an event occured more than once. Somewhat similar, I’m looking for a table showing all of the activities and the number of times in which that activity occured twice during the same case.

Has anyone found a way to implement such thing?

Kind regards,

Jeffrey


#2

Dear Jeffrey,

Thanks for your question.
Your problem will not be to get this count but the make sure that your “repetition count” takes into account the so called “Header activities” . Header activities are done once in the Header of an oder but impact all items. So when you have 100 lines in a sales order, a credit block will get a multiplication of 100 since it seen by 100 items = cases but was only done once.

Below you will find some code for # repetitions including typical Header activities we use.
(The purchase activities show up since we include all Sales Order releated purchasing in our O2C scheme).

=> CASE WHEN 

(SUM("_CEL_O2C_ACTIVITIES".“ACTIVITY_COUNT”)

COUNT(
DISTINCT
CASE WHEN “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” IN (‘Sales: Change Residental Delivery’,
‘Sales: Change Manually Set Billing Option’,
‘Sales: Change Net Weight’,
‘Sales: Change Total Weight’,
‘Purchasing: Change Vendor’,
‘Purchasing: Change Approval for Purchase Order’,
‘Purchasing: Change Currency’,
‘Purchasing: Send Purchase Order’,
‘Sales: Approve Credit Check’,
‘Sales: Deny Credit Check’,
‘Sales: Partially approve Credit Check’,
‘Sales: Set Delivery Block’,
‘Sales: Change Delivery Block’,
‘Sales: Remove Delivery Block’,
‘Sales: Print Invoice’,
‘Sales: Print Order Confirmation’)
OR “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” LIKE ‘Sales: Sales Order %’
THEN “_CEL_O2C_ACTIVITIES”.“MANDT” || “_CEL_O2C_ACTIVITIES”.“VBELN”
ELSE “_CEL_O2C_ACTIVITIES”."_CASE_KEY"
END
)

) < 0.0 THEN 0.0
ELSE
SUM("_CEL_O2C_ACTIVITIES".“ACTIVITY_COUNT”)

COUNT(
DISTINCT
CASE WHEN “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” IN (‘Sales: Change Residental Delivery’,
‘Sales: Change Manually Set Billing Option’,
‘Sales: Change Net Weight’,
‘Sales: Change Total Weight’,
‘Purchasing: Change Vendor’,
‘Purchasing: Change Approval for Purchase Order’,
‘Purchasing: Change Currency’,
‘Purchasing: Send Purchase Order’,
‘Sales: Approve Credit Check’,
‘Sales: Deny Credit Check’,
‘Sales: Partially approve Credit Check’,
‘Sales: Set Delivery Block’,
‘Sales: Change Delivery Block’,
‘Sales: Remove Delivery Block’,
‘Sales: Print Invoice’,
‘Sales: Print Order Confirmation’)
OR “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN” LIKE ‘Sales: Sales Order %’
THEN “_CEL_O2C_ACTIVITIES”.“MANDT” || “_CEL_O2C_ACTIVITIES”.“VBELN”
ELSE “_CEL_O2C_ACTIVITIES”."_CASE_KEY"
END
)
END

Have :grinning:!
Hans


#4

Hi,
if you just want to see a table with the activity names as dimension and the respective number of cases that run through an activity at least two times as a KPI, you could try the following:

COUNT(DISTINCT CASE WHEN ACTIVATION_COUNT("_CEL_P2P_ACTIVITIES"."ACTIVITY_EN") = 2 THEN "EKPO"."_CASE_KEY" ELSE NULL END)

If you want the number of cases that run through an actitvity exactly two times, you could use

COUNT(DISTINCT CASE WHEN ACTIVATION_COUNT("_CEL_P2P_ACTIVITIES"."ACTIVITY_EN") = 2 THEN "EKPO"."_CASE_KEY" ELSE NULL END)`
-
COUNT(DISTINCT CASE WHEN ACTIVATION_COUNT("_CEL_P2P_ACTIVITIES"."ACTIVITY_EN") = 3 THEN "EKPO"."_CASE_KEY" ELSE NULL END)

This will calculate the number of distinct cases that run through the activity at least two times and subtract the number of distinct cases that run through the activity at least three times.

Best regards,
Viana