Wrong value given by division

Hi there,

I’m getting the wrong values in two different cases when I’m dividing in celonis:

Case 1:
(
Sum(
CASE
WHEN ((“EBAN”.“MENGE”)/(“EKPO”.“MENGE”)) < 1 THEN 1
ELSE 0
END)
+
Sum(
CASE
WHEN ((“EBAN”.“MENGE”)/(“EKPO”.“MENGE”)) > 1 THEN 1
ELSE 0
END)
)
/
(1.0*COUNT_TABLE(EKPO))

Individually the first sum evaluates to 455. The second sum evaluates to 249. the numerator is then 704. The denominator comes to 3555.

The output should then be 704/3555 = 0.19803…
But the output on Celonis is 0.2068762…

Case 2:
SUM(CASE WHEN ISNULL(“EBAN”.“FLIEF”) = 0 AND “EBAN”.“FLIEF” != “EKKO”.“LIFNR”
THEN “EKPO”.“MENGE”
ELSE 0.0 END)
/
SUM(“EKPO”.“MENGE”)

The numerator comes out to 4069455
The denominator comes out to 1521749967

The result should be 0.0026741…
The result is 0.0077356…

Any ideas what’s causing the wrong values?

Did you check for the first case whether the sum in Celonis also comes to 704?

My approach would be to break down the problem (as you did) and then gradually build it back up to see where it goes wrong.

An issue that could be in play is the auto-join feature of Celonis. If you’re accessing only one table in a component/calculation, no join is done. If you use multiple tables then joins are executed as indicated in the data model. This is performed in a ‘left join’ way from the parent table. In some cases this might result in less records and hence different calculations.
See https://help.celonis.de/pages/viewpage.action?spaceKey=PQL45&title=Join+functionality for more details

1 Like

Hi there,

Joosbuijs is right, this is most probably connected to joins in your table, which joosbuijs described as ‘auto-joins’.

Not every entry in EKPO also has an entry in the EBAN table or in business words, not every purchase order item also has had a purchase requisiton item. When you combine these two tables in one formula, an inner join will be performed and only those items recognized which have entries in both tables, so only purchases with a purchase requisition.

This behaviour is very much wanted in the most cases, but for the few which are left we have our PU formulas. There is a beautiful entry on what they do and how they work here in this forum, just have a quick search for it.

Basically what PU_Formulas do is an (aggregated) left join of one column of a table to another, or in your case, left join EBAN.MENGE to the EKPO table by doing a PU_SUM(“EKPO”, “EBAN”."MENGE) / (“EKPO”.“MENGE”) for CASE 1 and a

CASE WHEN ISNULL(PU_FIRST(“EKPO”, “EBAN”.“FLIEF”)) = 0 AND PU_FIRST(“EKPO”, “EBAN”.“FLIEF”) != “EKKO”.“LIFNR”

for the second case, this should do the job.

Important Disclaimer: Please read up and inform yourself about PU_ Functions as these have special attributes and ways they work (e.g. analysis filter don’t usually filter them) and if they make sense in your situation.

Best Regards,

Benedict

Thanks for the detailed explanation! This solved the issue perfectly and the part about joins makes sense.

1 Like