Skip to main content

Hello,

I'm trying to make the DSO indicator, but I need to get data from several tables and I'm having problems...
The DSO formula is made up of two parts, Revenue and Accounts Receivable. I need to calculate each one individually and then divide one by the other.

I'll use the example of Accounts Receivable:
Quando eu uso os campos de Filtro, com os seguintes filtros, funciona sem problemas:

FILTER "o_celonis_SalesOrder"."CompanyCodeText" IN ('CompanyNameZYX');
FILTER "o_celonis_SalesOrder"."SalesDocumentType" IN ('XXXX', 'YYYY');
FILTER "o_custom_CustomerAccountItem"."BaselineDate" <= {t ${end_date}};
FILTER "o_custom_CustomerAccountItem"."ClearingDate" > {t ${end_date}} OR "o_custom_CustomerAccountItem"."ClearingDate" IS NULL

However, when I try to place the same filters in Case when, to generate the indicator that will be combined with Billing, there is a connection problem between the tables (o_celonis_SalesOrder and o_custom_CustomerAccountItem).

sum(
  CASE WHEN
    "o_celonis_SalesOrder"."CompanyCodeText" IN ('CompanyNameZYX')
    AND
    "o_celonis_SalesOrder"."SalesDocumentType" IN ('XXXX', 'YYYY')
    AND
    "o_custom_CustomerAccountItem"."BaselineDate" <= {t ${end_date}}
    AND
    (
      "o_custom_CustomerAccountItem"."ClearingDate" > {t ${end_date}}
      OR
      "o_custom_CustomerAccountItem"."ClearingDate" IS NULL
    )
    THEN "o_custom_CustomerAccountItem"."valor"
    ELSE 0
    END
)


Just to make it clear that the formulas are "the same", except that one is filter logic and the other is case when logic.
They are the same 4 conditions. And the second one doesn't work because of the common table error:
"No common table could be found. The tables ["o_celonis_SalesOrder"] and ["o_custom_CustomerAccountItem"] are connected, but have no common table. This means that they do not have a direct (or indirect) 1:N or N:1 relationship. Join path: [o_celonis_SalesOrder]N <-- 1![o_celonis_Customer]!1 --> N[o_custom_CustomerAccountItem]"
 

Hello filipe,

 

Can you please let us know if you are still having issues or if you were able to resolve this problem? I would like to document the solution here if you would be willing to share it with us.

I can explain why this behaviour is coming up. 

 

In the Filter you dont have an “AND” Statement so the filters are Looking on each Table seperatly, because of this seperatly Looking Celonis is not checking if the Connections in the Data Modell are making sense in the Background or not. 

 

In the Case When statement you do have the AND Statements, because of the AND Statements Celonis is checking the Connections in the Data Model in the Background. 

 

The good message is, you can easily solve the issue by using Bind or PU Functions. 

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

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

 

Kind regards Jonas

Community Code of ConductAccessibility statement