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,
- the new bottom table (the one with your suggestion) shows the company codes multiple times.
- image924576 19.2 KB
- 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
- image905331 5.86 KB
Is there a piece that i am missing?
Thanks,
Anna
Hello Anna,
- The first issue can be resolved by going to the settings of the OLAP table and check the box Distinct values
- 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,
- 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)
- 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
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?
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.
image763252 5.91 KB
Thanks for your support in this
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
