Skip to main content
Question

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.

  • November 13, 2024
  • 4 replies
  • 204 views
ines.cerve
Level 2
Forum|alt.badge.img

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 ["EKKN"] and ["EKPO"], since they do not have a 1:N relationship. You might want to make use of the BIND function. Join path: [EKKN]N <-- 1[EKPO]. For more information on the join path, search for "Join functionality" in PQL documentation.

 

Thank you in advance for your support!

 

    4 replies

    dxb.miner
    Level 5
    • dxb.miner
    • Level 5
    • November 14, 2024

    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")))

    ines.cerve
    Level 2
    Forum|alt.badge.img
    • ines.cerve
    • Author
    • Level 2
    • November 14, 2024

    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")))

     

    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.

    dxb.miner
    Level 5
    • dxb.miner
    • Level 5
    • November 14, 2024

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

    ines.cerve
    Level 2
    Forum|alt.badge.img
    • ines.cerve
    • Author
    • Level 2
    • November 14, 2024

    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!

    Community Code of ConductAccessibility statement