Adding a filter in/to column formula does not work

Question

Hi

I have a dimension such as order id or order number i.e. 12345 and I have another dimension that has 2 values Y and N...I want to count distinct the order id and then add a filter expression to get only the Y so I end up with # of orders that are only Y but for some reason I am getting the total number of orders, basically, the filter is being ignored.

Any idea?

Thanks

Joe

Joe Choueiri-Oracle · Answer

Thanks Jerry- case statement worked

Jerry Casey · Answer

I believe you need to move the filter function inside the Count Distinct formula.  If that doesn't work, A case statement like

CASE WHEN "...Common OU Opportunity Flag" = 'Y' then 1 ELSE 0 END always works.

Joe Choueiri-Oracle · Answer

Sorry Gianni, you are right.

I am talking about OBIEE 12C...keep in mind that I only have access to build analyses to certain subject area and do not have access to the RPD.

I have one fact and in this fact I have dimensions and one measure which is the amount....in the dimensions one of them is the opportunity id (i.e. 12345) then I have another dimension that flags if the oppty is Y/N and I am trying to count only the opptys that are Y

So the formula is

FILTER(COUNT(DISTINCT "Fact - Country Bookings Non-OU Opportunities"."Opportunity Name") USING ("Fact - Country Bookings Non-OU Opportunities"."Common OU Opportunity Flag" = 'Y'))

And, it is counting all the Y & N.

Hope this is clearer and thank you for your help as always.

Joe