Skip to main content
Solved

I would like to count how often a Sales Order (Header) goes out of a credit block. The issue is while this activity happens at the header level, I only manage to get my KPI at the item level. Which means that it is multiplied by the number of lines.

  • June 9, 2021
  • 6 replies
  • 14 views
M
Level 2
Forum|alt.badge.img+10

So, I tried to solve it with a pull-up function.

 

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

 

But the results do not seem correct to me:

  1. I do not think this offers a distinct value
  2. when putting the KPI in an OLAP against the customer, I get duplicates for the customer.

 

Another option that I could think of, made use of match_activity. However, this is then still at line item level.

Best answer by Anonymous

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

6 replies

A
  • Anonymous
  • 0 replies
  • June 10, 2021

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

M
Level 2
Forum|alt.badge.img+10
  • mel.schic11
  • Author
  • Level 2
  • 3 replies
  • June 10, 2021

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.

M
Level 2
Forum|alt.badge.img+10
  • mel.schic11
  • Author
  • Level 2
  • 3 replies
  • June 10, 2021

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.

A
  • Anonymous
  • 0 replies
  • June 10, 2021

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

A
  • Anonymous
  • 0 replies
  • Answer
  • June 11, 2021

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

M
Level 2
Forum|alt.badge.img+10
  • mel.schic11
  • Author
  • Level 2
  • 3 replies
  • June 11, 2021

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!


Community Code of ConductAccessibility statement