Case Statement that is not generation a list of values

Joe Choueiri-Oracle

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

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

Joel

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

Gianni Ceresa

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

Hi Joel

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

Joe Choueiri-Oracle

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]

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

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