Calculation of average cases per user in a number component

Hello,

I am trying to calculate the average number of posted invoices per user in a number component in the AP data model. The basic code of the KPI looks as follows:

COUNT(“BKPF”.“USNAM”) / COUNT(DISTINCT(“BKPF”.“USNAM”))

The current KPI however does not yet show the real performace of the AP department because many invoices are posted via batch files or batch users and some invoices are posted by users that are doing an internship or an apprencticeship and these users do not post more than for example 100 invoices.

In order to get rid of the cases which were posted by batch users, I used a component filter which works fine. But for the second issue (exclusion of cases which were posted by users that have not posted more than 100 invoices in the selected time range) I have not yet found a solution. I have tried several case when statements and also worked with ABC clusters to exclude the cases of these users, but nothing worked.

Does anybody of you have an idea how this could be solved? I am open for all kinds of proposals.

Best regards
Thomas

Hello Thomas,

So, if I understand you correctly. You want to create a filter that excludes those “BKPF”.“USNAM” values for which the number of invoices they processed in a certain time window is smaller than 100?

I have interpreted “selected time range” as being between two fixed dates. Please write back if this is not what you want.

The filter conditions I would recommend using is:

FILTER PU_COUNT(DOMAIN_TABLE (“BKPF”.“USNAM”), “BKPF”.“USNAM”,
DATE_BETWEEN(PU_FIRST(“BKPF”, “Activity table”.“EVENTTIME”
“Activity table”.“ACTIVITY_EN” = ‘post invoice activity name’),
TO_TIMESTAMP(‘date of start of time window’, ‘DD/MM/YYYY’),
TO_TIMESTAMP(‘date of end of time window’, ‘DD/MM/YYYY’)) = 1
) > 100;

Does this answer your question?

Best wishes,

Calandra

Hello Calandra,

thanks for your quick response. The way you understood my request was correct. However the filter for the eventtime is not done on component level but on app level in our case.

I used your filter therefore as follows:

FILTER PU_COUNT(DOMAIN_TABLE (“BKPF”.“USNAM”), “BKPF”.“USNAM”) > 100;

Unfortunately the result is ‘-’ in this case even though there are dozens of users which have posted more than 100 invoices. Do you see any other possibilites as alternative solutions for this problem?

Best regards
Thomas

Hello Thomas,

So said in your first post that you want “calculate the average number of posted invoices per user”?

I would do that using a Pull function. i.e.

AVG(PU_COUNT_DISTINCT(DOMAIN_TABLE (“BKPF”.“USNAM”), ”Activity table”._CASE_KEY”,
“Activity table”.“ACTIVITY_EN” = ‘post invoice activity name’))

In this code for each row in “BKPF”.“USNAM” the number of distinct case keys which have an post invoice activity are counted and it averages over these counts.

Now as you said you don’t want to use a filters code needs to be modified to build the restrictions into the KPI itself.

The first restriction you mentioned you wanted is to only Post invoice activities took place between two dates. This can be done just by adding another condition on to the PU function:

AVG(PU_COUNT_DISTINCT(DOMAIN_TABLE (“BKPF”.“USNAM”), “Activity table”."_CASE_KEY",
“Activity table”.“ACTIVITY_EN” = ‘post invoice activity name’ AND
DATE_BETWEEN(PU_FIRST(“BKPF”, “Activity table”.“EVENTTIME”,
“Activity table”.“ACTIVITY_EN” = ‘post invoice activity name’),
TO_TIMESTAMP(‘date of start of time window’, ‘DD/MM/YYYY’),
TO_TIMESTAMP(‘date of end of time window’, ‘DD/MM/YYYY’)) = 1))

Now finally you said you want the average to calculated over only those users who post more than a certain number of invoices (I’ll use 100 as an example). I would do this with the following case when statement:

AVG( CASE WHEN
PU_COUNT_DISTINCT(DOMAIN_TABLE (“BKPF”.“USNAM”), “Activity table”."_CASE_KEY",
“Activity table”.“ACTIVITY_EN” = ‘post invoice activity name’ AND
DATE_BETWEEN(PU_FIRST(“BKPF”, “Activity table”.“EVENTTIME”,
“Activity table”.“ACTIVITY_EN” = ‘post invoice activity name’),
TO_TIMESTAMP(‘date of start of time window’, ‘DD/MM/YYYY’),
TO_TIMESTAMP(‘date of end of time window’, ‘DD/MM/YYYY’)) = 1) > 100
THEN
PU_COUNT_DISTINCT(DOMAIN_TABLE (“BKPF”.“USNAM”), “Activity table”."_CASE_KEY",
“Activity table”.“ACTIVITY_EN” = ‘post invoice activity name’ AND
DATE_BETWEEN(PU_FIRST(“BKPF”, “Activity table”.“EVENTTIME”,
“Activity table”.“ACTIVITY_EN” = ‘post invoice activity name’),
TO_TIMESTAMP(‘date of start of time window’, ‘DD/MM/YYYY’),
TO_TIMESTAMP(‘date of end of time window’, ‘DD/MM/YYYY’)) = 1)
ELSE NULL END)

In this code the same code block was used twice. First to exclude rows where the count is too low and then to calculate the values of the rows that aren’t excluded to use in the average.

Here cases where the activity post invoice is carried out by the same user more than once for the same activity are only counted once. If you want to each count repetitions individually just use PU_COUNT instead of PU_COUNT_DISTINCT.

I hope this makes sense and solves your problem.

Best wishes,

Calandra

Hello Calandra,

this solution worked perfectly! Thank you very much for your help! Much appreciated!

Best regards
Thomas

1 Like