Calculation of average cases per user in a number component

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 dont 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 (Ill 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 arent 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