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

Case Statement that is not generation a list of values

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

Hi

Need your help, not sure if I have been through his before but can not recall.

This is my case statement

CASE

WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" > 10000000 THEN '1. > $10M'

WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 5000001 AND 10000000 THEN '2. $5M-$10M'

WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 1000001 AND 5000000 THEN '3. $1M-$5M'

WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 500000 AND 1000000 THEN '4. $500K-$1M'

WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 350000 AND 500000 THEN '5. $350-$500K'

WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 200000 AND 350000 THEN '6. $200k-$350k'

WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" < 200000 THEN '7. < $200k' ELSE '7. < $200k'

END

But for some reason, if I try to filter on any of the values, I get this, no drop down with values

pastedImage_0.png

Which causing also not to see most values when I try to create a prompt with this case statement, any idea?

My goal is to create a prompt with this statement and end up with checkboxes where users can check <200k or/and 200k-350k etc

Thanks

Joe

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    What happens when you place this CASE statement column in an analysis? Does it return any data?

  • Gianni Ceresa
    Gianni Ceresa Mod

    I assume your model is clean, and therefore that "USD Amount" is a real fact with an aggregation rule.

    If you think at it as a measure (which is probably what it is), you easily see why it behave differently than what you think. That column isn't the value of a single row most of the time, it's probably a SUM. That's probably why it doesn't behave as you expect, because it keeps changing value depending on the point of view of your analysis: the dimensions / attributes around this column define the aggregation it has, driving the values.

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

    Hi Joel

    Yes it does return data, the values in the statement.

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

    Hi Gianni

    So this is a normal behavior when it comes to having a case statement with a measure (with aggregation rule), no work around to use it as a prompt and produces values for users to select?

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner 
    Joe Choueiri-Oracle wrote:So this is a normal behavior when it comes to having a case statement with a measure (with aggregation rule)

    Yes. It's a measure. What you want to do i an operation on an attribute.

    Joe Choueiri-Oracle wrote:no work around to use it as a prompt and produces values for users to select?

    Not "workaround", but "solution": Measures and aggregates are the two core concepts that all dimensional modelling and analytics.

    You need dimensional attribute which represents what you want. In the RPD, in a dimension  - even if "only" a degenerate dimension. Not in the front-end.

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

    Hi Christian

    Understood, thank you for taking the time to explain.

    I will need to talk to my developers because I do not have access to the RPD, I only have edit / answer access at the front end.

    Thanks again

    Joe