Calculate average number of activities for croped selection

Dear Colleagues,

I am trying to calculate average workload for a selected subprocess within the whole process.
For that I wish to assess the average number of activities between specificed SOURCE and TARGET.
It should show that from the moment invoice is received by AP accountant up till the moment invoice is posted, certain number of additional steps has to be executed. I wish to present it in various dimensions (for example in OLAP table by company code as dimension).
I tried the formula presented in attached picture, but unfortunately it does not work.
How can this scenario be applied?
Thank you in advance for any suggestions.

Hello ksitarsk,

the reason for the error message is that you cannot use SOURCE(…) and TARGET(…) together with a dimension of the activity table without the SOURCE or TARGET. So if you wrap the second argument of the PU function into a SOURCE or TARGET, the error should be gone.

However, I think the SOURCE/TARGET approach is not what you want to solve your task. If you use the condition that SOURCE should be the “AP received” and TARGET should be the “Invoice posted” activity, you will count, per case, how often “AP received” is directly followed by “Invoice posted”.

In order to count the number of activities between the two given activities, you could use CALC_CROP:

SUM ( CALC_CROP( FIRST_OCCURRENCE [ 'AP received' ] TO LAST_OCCURRENCE ['Invoice posted'], "_CEL_AP_ACTIVITIES"."ACTIVITY_EN" ) ) 
/ COUNT_TABLE("BSEG")

CALC_CROP will return a 1 for every activity between the first occurrence of “AP received” and the last occurrence of “Invoice posted” (you can also use FIRST_OCCURRENCE here, depending on what you want to have) per case. All values outside of the range are NULL. So by summing up the values, you get the overall number of activities you’re interested in, and by dividing it by the case count, you get the average number per case. Note that in this solution, the AP received" and “Invoice posted” activities are included in the count. If you don’t want to count them we would need to add a CASE WHEN statement which removes or subtracts those again.

You can add a dimension to group it accordingly, for example per case or per company code.

Hope that helps!

David

1 Like

Thank you very much for the detailed instruction! I will try to apply it accordingly and let you know in case of any further issues.