Skip to main content
Question

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

  • May 18, 2020
  • 10 replies
  • 47 views
.
Level 6

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:

image744307 5.36 KB

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

10 replies

.
Level 6
  • .14600775
  • Author
  • Level 6
  • May 18, 2020

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.

.
Level 6
  • .14600775
  • Author
  • Level 6
  • May 24, 2020

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

V
Level 0
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
.
Level 6
  • .14600775
  • Author
  • Level 6
  • May 29, 2020

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. image924576 19.2 KB
  3. 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
  4. image905331 5.86 KB

Is there a piece that i am missing?

Thanks,

Anna

C
Level 0

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:
  3. Is it a many to one relationship from BSEG to CASES?
  4. Through which fields/columns are they connected?
  5. What is the primary key of Cases?
  6. 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

.
Level 6
  • .14600775
  • Author
  • Level 6
  • June 2, 2020
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.
image1284205 10.7 KB 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
V
Level 0

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

.
Level 6
  • .14600775
  • Author
  • Level 6
  • June 5, 2020
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?
.
Level 6
  • .14600775
  • Author
  • Level 6
  • June 5, 2020

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.

https://emoji.discourse-cdn.com/twitter/frowning.png?v=9image763252 5.91 KB

Thanks for your support in this

https://emoji.discourse-cdn.com/twitter/slight_smile.png?v=9

C
Level 0

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

Community Code of ConductAccessibility statement