Filter count() is not working — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Filter count() is not working

Received Response
163
Views
4
Comments
Saro
Saro Rank 5 - Community Champion
edited Aug 13, 2024 6:16PM in Oracle Analytics Cloud and Server

Hi friends,

Im in obiee 11.1.1.7.150120. In my analysis i have a count(DISTINCT "Fact1-payroll"."Employee Number") in my edit formula which is equal to 4 and that analysis has the following filters like

filter 1.PNG

If i try to display the same count of employee number 4 in my one of my formula column with the below expression like

FILTER(FILTER(FILTER(count(DISTINCT "Fact1-payroll"."Employee Number") USING ("Fact1-payroll"."Period Name" IN ('@{v_from_payroll_period}'))) USING ("Fact1-payroll"."Employee Number" IN ('@{v_employee_number}'))) USING (EVALUATE('to_char(%1,%2)' as char, "Fact1-payroll"."Pyroll Date", 'DD-MON-YYYY')||'('||EVALUATE('to_char(%1)' as char, "Fact1-payroll"."Request Id")||')' IN ('@{v_from_payroll_run}')))

With the above expression, im not getting the correct employee number count as 4, as it is displaying other wrong value.

What could be the issue with my filter(count) which results in wrong values. Does any other condition with my filter expression is missing behind. Kindly advice friends.

Thanks in advance.

Regards,

Saro

Answers

  • Saro
    Saro Rank 5 - Community Champion

    Hi guys,

    Any Suggestions.

    Saro

  • Saro
    Saro Rank 5 - Community Champion

    Dear Friends,

    Seems like a product limitation with OBIEE

    Bug 21571659 - FILTER FUNCTION DOESN'T WORK AS EXPECTED IN OBI -

    Unable to use "COUNT DISTINCT" with the "FILTER" function ( Doc ID 2138129.1 ) - As per these, filter function in OBIEE does not execute each row line-by-line like the case statement, which is causing the issue


    Thanks,


    Regards,

    Saro

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    dear saro,

    what is your requirement. did you try first  filter then count?

    count(distinct (Filter )

    Thanks

    Aj

  • Saro
    Saro Rank 5 - Community Champion

    Dear AJ,

    Thanks for the reply. Glad in a getting a response from you almost after an year where you helped for one date calculation logic with OBIEE:-)

    My requirement is like below:

    Im in obiee 11.1.1.7.150120. In my analysis i have a count(DISTINCT "Fact1-payroll"."Employee Number") in my edit formula which results to 4(correct value) and that analysis has the below filters

    Filter 1-AJ.PNG

    Similarly if i try to substitute the Filter by condition to display the same count 4 with the below expression, then the Filter(count) is not working in which from the SR they confirmed me like a product limitation in calculating filter by expression for the count.

    (FILTER(FILTER(FILTER(count(DISTINCT "Fact1-payroll"."Employee Number") USING ("Fact1-payroll"."Period Name" IN ('@{v_from_payroll_period}'))) USING ("Fact1-payroll"."Employee Number" IN ('@{v_employee_number}'))) USING (EVALUATE('to_char(%1,%2)' as char, "Fact1-payroll"."Pyroll Date", 'DD-MON-YYYY')||'('||EVALUATE('to_char(%1)' as char, "Fact1-payroll"."Request Id")||')' IN ('@{v_from_payroll_run}'))))

    With the above expression im not getting the correct count as 4. This is the problem.

    Kindly look into it and let me know the possible resolution. Hope you understand the scenario.

    Thanks in advance

    Regards,

    Saro