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

Adding a filter in/to column formula does not work

Received Response
128
Views
4
Comments
Joe Choueiri-Oracle
Joe Choueiri-Oracle Rank 5 - Community Champion
edited Aug 13, 2024 5:17PM in Oracle Analytics Cloud and Server

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

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi Joe,

    You will need to do an extra effort ...

    First by being explicit on the product and version (OBIEE and Publisher are packaged together while being 2 very different products).

    Joe Choueiri-Oracle wrote:... for some reason I am getting the total number of orders, basically, the filter is being ignored.Any idea?

    No idea if you don't at least post the formula you wrote and as many details as possible.

    Random guessing answers aren't going to help you...

  • Joe Choueiri-Oracle
    Joe Choueiri-Oracle Rank 5 - Community Champion

    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

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    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
    Joe Choueiri-Oracle Rank 5 - Community Champion

    Thanks Jerry- case statement worked