Hi,
These days I struggled with these nested aggregation, to calculate how many customers has 1 to 10 orders. In fact it is Aggregate of a conditional aggregate.
In OBIEE 11.1.1.9 BMM model I have classic star schema with Fact Orders and Dim Customers. I create one Level based measure in Fact Orders:
with Aggregation rule: Count distinct on ORDER_ID and Level BY Customer Details.
Then I try to create this formula in Analysis:
CASE WHEN "Orders"."Orders_count" BETWEEN 1 AND 10 THEN "Customer"."Customer ID" ELSE NULL END with count distinct Aggregation rule. Unfortunately this gives me many rows of 1 and 0 for each Customer Country, instead of one line with the number of unique customers. Like it returns a line check for each client, but I need the data to be aggregated in the end. What I'm missing here? Can you help me?
