Count Cases works on OLAP table, but cannot get total count in a summary view - single line invoice

Dear Celonis Community,

I am trying to count how many invoices have single line items, and how many have more then 2, 3, etc.

I am able to get the formula to work when i create a OLAP table as per the below:

The Dimension = “BSEG”."_CASE_KEY"
KPI = COUNT_TABLE(“BSEG”)

In the third column I have the below code that produces the correct result as indicated in my table.

case when COUNT(“BSEG”."_CASE_KEY")=1 then 1 else 0 end

However, my intention is to count the total number of cases where invoice has only 1 line item. When i remove the dimension of Case_Key and replace it with “CASES”.“CompanyCode”, it produces a zero value.

I have tried different statements such as:
COUNT(
CASE WHEN PU_COUNT(“CASES”, “BSEG”."_CASE_KEY") = 1
THEN “BSEG”."_CASE_KEY"
ELSE NULL
END
)
But this only produces a count of ‘0’.

Can you please advise what i am doing wrong, and how i can correct for this error.

Thanks,
Anna

To add to this, i have a set of filters in the component section. I have adjusted my code to reflect this in case it makes a different, however, the count it retrieves is still ‘0’

COUNT(
CASE WHEN LEFT(“BSEG”.“HKONT”,3) NOT IN (‘001’,‘002’,‘003’,‘004’) AND left(“CASES”.“VendorNumber”,3) NOT IN (‘080’)
AND “CASES”.“FIMM” = ‘FI’
AND PU_COUNT(“CASES”, “BSEG”."_CASE_KEY") = 1
THEN “BSEG”."_CASE_KEY"
ELSE NULL
END
)

Please help.

Hi All,

I keep trying different statements such as the below:

COUNT (DISTINCT CASE WHEN PU_COUNT(DOMAIN_TABLE(“CASES”.“CompanyCode”, (“BSEG”.“BSEG_ID”)), (“BSEG”.“BSEG_ID”)) = 1
THEN “BSEG”."_CASE_KEY"
ELSE NULL
END)

  • (subtract)
    COUNT (DISTINCT CASE WHEN PU_COUNT(DOMAIN_TABLE(“CASES”.“CompanyCode”, (“BSEG”.“BSEG_ID”)), (“BSEG”.“BSEG_ID”)) > 1
    THEN “BSEG”."_CASE_KEY"
    ELSE NULL
    END)

and am still not getting the correct result.

Can someone please point me in the right direction?

Thanks,
Anna

Hello Anna,

In order to count the total number of cases where invoice has only 1 line item try to create the following OLAP table:
Dimension: "CASES"."CompanyCode"
KPI: PU_COUNT(DOMAIN_TABLE("CASES"."CompanyCode"), "CASES"."_CASE_KEY", PU_COUNT("CASES", "BSEG"."_CASE_KEY") = 1)

The condition PU_COUNT(“CASES”, “BSEG”."_CASE_KEY") = 1
inside the main PU_COUNT function ensures that only cases where the invoice only have a single line item are included.
Then the term DOMAIN_TABLE("CASES"."CompanyCode"), "CASES"."_CASE_KEY" counts all the cases for each CompanyCode which satifies the condition PU_COUNT("CASES", "BSEG"."_CASE_KEY") = 1

Hope this helps,
Viana

Hi Viana,

thanks for your input. I have tried your suggestion, but the results still just show ‘0’ for all company codes.

As you can see from the below table, there are 2 issues,

  1. the new bottom table (the one with your suggestion) shows the company codes multiple times.

  2. when i select an invoice that i know should have a count of 1 and thus represented in your table, the value is still ‘0’

Is there a piece that i am missing?

Thanks,
Anna

Hello Anna,

  1. The first issue can be resolved by going to the settings of the OLAP table and check the box “Distinct values”

  2. In order to understand why it did not work and to provide you an alternative solution, could you please answer the following questions:
    Is it a many to one relationship from “BSEG” to “CASES”?
    Through which fields/columns are they connected?
    What is the primary key of “Cases”?
    What fields are used to create the “CASES”."_CASE_KEY"?

Also, It would be helpful if you can provide a screenshot of how the “CASES” and “BSEG” tables are connected.

Best regards,
Your Data Science Team

Hello,

Thank you for your quick response.

To answer your questions,

  1. yes, there is a many to one relationship from “BSEG” to “CASES”. However, when I used this filter, FILTER LEFT(“BSEG”.“HKONT”,3) NOT IN (‘001’,‘002’,‘003’,‘004’); Then the relationship shows a 1 to 1 ratio, when its a single line item invoice, and a 2 or more to 1 ratio when its a multiple line item. (Hence the type of information i am trying to achieve)
  2. Below showcases the connections between the two tables.
    image

The BSEG table also has a company code field : “BSEG”.“BUKRS” Perhaps this can be used instead of the “CASES”.“CompanyCode”

_CASE_KEY is a function of client, company code, invoice number & fiscal year.

Please let me know if the above information helps, or if you need anything further.

Thanks,
Anna

Hi Anna,

thanks for clarification, so BSEG._CASE_KEY is on invoice level an BSEG.BSEG_ID on invoice line item, correct?

To count the invoice lines per invoice you would need to use following formula:

COUNT(DISTINCT CASE WHEN PU_COUNT(DOMAIN_TABLE( BSEG._CASE_KEY), 
BSEG.BSEG_ID,
 PU_COUNT(DOMAIN_TABLE( BSEG._CASE_KEY), BSEG.BSEG_ID) = 1) >= 1
THEN BSEG._CASE_KEY ELSE NULL END)

Best regards,
Viana

Hi Viana,

The suggestion still produce results with ‘0’ for the count.

Should i be incorporating the FILTER LEFT(“BSEG”.“HKONT”,3) NOT IN (‘001’,‘002’,‘003’,‘004’) within the code?

Also, does it not matter that its not pulling the count into the company code?

image

Hi Viana,

I noticed that even when i add the “BSEG”.“BSEG_ID” field as part of the dimension, the code still doesnt work.

To compare, as per the screen shot below, the top table is my original view with the correct results. It has BSEG Case_key as the dimension and the count of table BSEG tells me that there is only 1 BSEG_ID for that case key. When looking at the highlighted ID and comparing the two tables, I know the count should be 1, but the results are still indicating zero. :frowning:

Thanks for your support in this :slight_smile:

Hello Anna,

what is your code for the company code dimension? Did you try to use “BSEG”.“BUKRS” as you mentioned above?
You could also try it one time without the filter (FILTER LEFT(“BSEG”.“HKONT”,3) NOT IN (‘001’,‘002’,‘003’,‘004’)) to see if this is the problem.

Best regards,
Your Data Science Team