Error: The aggregation function SUM is applied on a column from table "EKPO" which has a 1:N relationship to the common table "EKKN". This means that one input value can contribute to the aggregation result multiple times.

Hi everyone!

I'm having issues with a formula, can you please help or tell who can help me?

My goal is to have an OLAP with fields from EKKN and EKPO. I need a KPI to sum the PO item values in EUR.

Formula: SUM("EKPO"."NETWR_CONVERTED" )

Error: The aggregation function SUM is applied on a column from table "EKPO" which has a 1:N relationship to the common table "EKKN". This means that one input value can contribute to the aggregation result multiple times. For more information search for "Join functionality" in PQL documentation.

I've checked the results and, in some cases, it is summing the same value more than once, as predicted by the error.

The following solutions did not work:

SUM ( CASE WHEN INDEX_ORDER ("EKPO"."NETWR_CONVERTED",PARTITION BY (FILTER_TO_NULL ("EKPO"."NETWR_CONVERTED"))) = 1 THEN "EKPO"."NETWR_CONVERTED" ELSE NULL END)

https://docs.celonis.com/en/sum.html

PU_SUM("EKKN","EKPO"."NETWR_CONVERTED")

Error: Pull-Up-function could not be executed. PU_SUM: Cannot execute PU-function on tables o"EKKN"] and K"EKPO"], since they do not have a 1:N relationship. You might want to make use of the BIND function. Join path: oEKKN]N <-- 1NEKPO]. For more information on the join path, search for "Join functionality" in PQL documentation.

Thank you in advance for your support!

Page 1 / 1

For each MANDT-EBELN-EBELP combination in EKPO, there will be more than one line item in EKKN. So, if you have the EKKN table column as dimension in OLAP and want to aggregate NETWR from EKPO, you can take unique NETWR from EKPO.

Try using below formula, it might work,,

SUM(PU_FIRST(DOMAIN_TABLE(EKKN.MANDT, EKKN.EBELN, EKKN.EBELP), BIND("EKKN","EKPO"."NETWR_CONVERTED")))

Try using below formula, it might work,,

SUM(PU_FIRST(DOMAIN_TABLE(EKKN.MANDT, EKKN.EBELN, EKKN.EBELP), BIND("EKKN","EKPO"."NETWR_CONVERTED")))

Thank you for your help.

Unfortunately, the formula also doesn’t work.

I received this error:

The aggregation function SUM is applied on a column from table <DOMAIN_TABLE ( "EKKN"."MANDT" , "EKKN"."EBELN" , "EKKN"."EBELP" )> which has a 1:N relationship to the common table "EKKN". This means that one input value can contribute to the aggregation result multiple times. For more information search for "Join functionality" in PQL documentation.

I’ve checked the results and I still have duplicated values.

May I know which columns you want to include as dimensions in the OLAP from EKKN and EKPO tables?

Hello,

Approver: PU_LAST("EKPO","_CEL_P2P_ACTIVITIES"."USER_NAME", "_CEL_P2P_ACTIVITIES"."ACTIVITY_EN" IN ('Release Purchase Requisition'))

GL Account: "EKKN"."SAKTO"

Cost Center: "EKKN"."KOSTL"

Supplier: "LFA1"."LIFNR" || ' - ' || "LFA1"."NAME1"

PR: "EKPO"."BANFN"

PO: "EKPO"."EBELN"

Total amount in EUR: what I need help

Thank you!

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN