Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

how to put a Countif in a unique field in an OTBI report

Received Response
1138
Views
8
Comments
Abhishek Bagh
Abhishek Bagh Rank 4 - Community Specialist
in Oracle Transactional Business Intelligence

Summary:

In my report, I have Person Number field which is unique, however the report has some duplicate entries. I only need to understand how do I get a count to see how many duplicates I get through Person Number field.

What formula do I use to know the count of each Person Number row like we get through Count if in excel.

Content (required):


Version (include the version you are using, if applicable):


Code Snippet (add any code snippets that support your topic, if applicable):

Tagged:

Answers

  • Amrita Gupta-Oracle
    Amrita Gupta-Oracle Rank 6 - Analytics Lead

    Hi @Abhishek Bagh ,

    You can try below formulae -

    MAX(RCOUNT(<"Folder"."Column"> BY <"Folder"."Column">) BY <"Folder"."Column">)

    However, the value for this count column will also repeat, as below. I have used the column "Invoice ID" for my test(in place of Person Number) -

    MAX(RCOUNT("- General Information"."Invoice ID" BY "- General Information"."Invoice ID") BY "- General Information"."Invoice ID")


  • Abhishek Bagh
    Abhishek Bagh Rank 4 - Community Specialist

    Hi @Amrita Gupta-Oracle - thanks so much for your response, this is really helpful.

    However, when i applied this, i do get results like below (which is what you mentioned in your response already). Wondering: is it feasible to get an output for the duplicate records as 1,2,3.. intent is to take them out through the report.

    if we could sequence nos. for these duplicates, i will then use a combination to exclude them from my report. Many thanks though for your assistance.

    Regards,

    Abhishek

  • Amrita Gupta-Oracle
    Amrita Gupta-Oracle Rank 6 - Analytics Lead

    Use below formulae for the sequence -

    RCOUNT("- General Information"."Invoice ID" BY "- General Information"."Invoice ID").

    You will get result as below

    Please accept the answer if this helps resolve your query.

  • Abhishek Bagh
    Abhishek Bagh Rank 4 - Community Specialist

    Hi @Amrita Gupta-Oracle - I am afraid, because when I use this one, i only see value as 1 for all records (incl. duplicates).. am i missing something in here?

  • Amrita Gupta-Oracle
    Amrita Gupta-Oracle Rank 6 - Analytics Lead

    Hi Abhishek,

    Have you removed the column which was causing Person Number to repeat?

  • Abhishek Bagh
    Abhishek Bagh Rank 4 - Community Specialist

    Hi @Amrita Gupta-Oracle - No I didn't. I feel simple RCount is not providing what you see in the results at your end.

    Regards,

    Abhishek

  • Abhishek Bagh
    Abhishek Bagh Rank 4 - Community Specialist

    Hi @Amrita Gupta-Oracle - Also, in the meantime, i tried doing some alternatives to do the exclusion.

    I put up below formula so that i could exclude 1 & keep only 0 thru filter in Criteria page however when i see the output for below formulae, it only shows 0 for all records (whereas it should show 1 for 4 persons). Not sure what am i missing in here, can you advise pls?

    case when MAX (RCOUNT ("Worker"."Person Number" BY "Worker"."Person Number") BY "Worker"."Person Number")>1 AND "Worker"."Termination Date" IS null then 1 else 0 end

    Regards,

    Abhishek

  • Amrita Gupta-Oracle
    Amrita Gupta-Oracle Rank 6 - Analytics Lead

    Hi Abhishek,

    Could you try applying the same filter using selection steps, instead of doing it using a FILTER. The selection step filters are applied right at the end, so it would do so after the RCOUNT is correctly calculated.

    I will try to replicate from my end and let you know.