Hi Mel,

did you try the DISTINCT statement? PU_COUNT_DISTINCT: Calculates the number of distinct elements in the specified column for each element of the given child table.

regards

Marc

Hi Marc, thank you for your reply.

When using:

PU_COUNT_DISTINCT("VBAK", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN ('Credit Approval Release') ) 

The result is '1', my interpretation of this is that overall headers within the data the distinct count is '1'. When applying the dimension of customer this then is also '1'. While what I would like to see is the aggregation over the headers per vendor.

While entering my previous post, I thought of the option to add SUM():

SUM(PU_COUNT_DISTINCT("VBAK", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN ('Credit Approval Release')))

The results for this are plausible. I will validate my results and think about the logic of this. Once it is clear to me I will post another reply.

Still if anyone has an explanation or a better approach, please feel free to comment.

Hi Mel,

I think you could use PU_COUNT_DISTINCT("KNA1", "VBAK.VBELN", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN ('Credit Approval Release') ) 

it will count per customer (as available in customer master table KNA1) the number of sales order lines containing selected activities with aggregation at header level (VBAK.VBELN). The DISTINCT statement will ensure that each Sales Order is counted once even if there are multiple lines.

regards

Marc

Hi Mel,

with formula PU_COUNT_DISTINCT("KNA1", "VBAK.VBELN", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN ('Credit Approval Release') ), you will get customers that don't have any orders going through this step, but also inactive customers. You may add a set of filters based on customer master fields (like flag for deletion LOEVM or block SPERR) to keep only the active ones. Yet you would still have customers with 0 entries because they don't have any orders.

If you want to check on the customer base that have placed orders, you can use the DOMAIN_TABLE statement:

PU_COUNT_DISTINCT(DOMAIN_TABLE("VBAK"."KUNNR"), "VBAK.VBELN", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN ('Credit Approval Release') )

but then you also need to check 'distinct values' in the Advanced Options of your OLAP table.

0 values will be for customers that have placed orders which don't have a 'Credit Approval release' activity.

regards

Hi Marc, the last adjustment does the trick.

For anyone interested:

PU_COUNT_DISTINCT(DOMAIN_TABLE("VBAK"."KUNNR"), "VBAK"."VBELN", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN ('Credit Approval Release') )

Another (and much less elegant option) I found is creating an OLAP with dimension Vendor and KPI COUNT_TABLE("VBAK"). If you put a component filter on the activity over that table you get the same results. Which makes sense. However, the formula is much better as you can use it down the line as well.

Thank you for your help!