Skip to main content
Question

Subtract two easy formulas does not work. why?

  • January 5, 2021
  • 3 replies
  • 7 views
steven.bernd
Level 6
Forum|alt.badge.img+1

Hello everybody,

i want to subtract two simple COUNT formulas but it doesnt work.

However, if I look at the individual results, then I have a result.

Funny

image996864 63 KB

image273758 4.34 KB image287723 4.39 KB

what am I doing wrong?

Thx

Steven

3 replies

P
Level 2
Forum|alt.badge.img+7
Hi StevenB, Could you please try the same using COUNT_TABLE. COUNT_TABLE(table1.column1) - COUNT_TABLE(table2.column2). Hope it will solve your problem. Thanks!! Pooja
steven.bernd
Level 6
Forum|alt.badge.img+1
  • steven.bernd
  • Author
  • Level 6
  • January 8, 2021
Many Thanks. Now I have a number as a result. Thats really great. Unfortunately, the number is completely wrong (-141). What is so difficult for Celonis to subtract 2 numbers? Greetings
D
Level 1
Forum|alt.badge.img+2

Hi @StevenB

With your query, you are introducing a join between the two tables. After the join was computed, the aggregation functions are applied. So the COUNT on the smaller table actually returns the number of rows after the join with the larger table - which is basically the number of rows of the larger table (not necessarily exactly, because NULL values are not being counted). But in your case, it returns 0 because both COUNT results are the same, because of the mentioned reason.

If you are familiar with SQL: There you would also have the same issue. If you do a Join between both tables and then use the COUNTs in the SELECT statement, you would end up with the same result.

The suggestion of @pooja1 is good, as COUNT_TABLE counts the number of rows of a particular table, not taking potential joins into account. However, if you have NULL values in the columns, they are also counted here, in contrast to the COUNT functions, which is probably why you get a result which is not the one you are expecting.

So what you wanna do is, you want to calculate the COUNTs separately first before subtracting, such that no join is introduced. You can do this for example with a PU function using the CONSTANT child table option:

PU_COUNT(CONSTANT(), "Table1"."Column") - PU_COUNT(CONSTANT(), "Table2"."Column")

If FILTERs should be taken into account, you can warp a FILTER_TO_NULL around the arguments:

PU_COUNT(CONSTANT(), FILTER_TO_NULL("Table1"."Column")) - PU_COUNT(CONSTANT(), FILTER_TO_NULL("Table2"."Column"))

If you want, you can also do this for one side only, for the other one you could also leave the COUNT, e.g.:

COUNT("Table1"."Column") - PU_COUNT(CONSTANT(), FILTER_TO_NULL("Table2"."Column"))

Can you check if this works for you?

Best

David

Community Code of ConductAccessibility statement