Filter count() is not working

Question

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

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

Saro · Answer

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

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

asim cholas · Answer

dear saro,

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

count(distinct (Filter )

Thanks

Aj

Saro · Answer

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