6 Replies Latest reply on Jul 31, 2017 9:28 AM by KonstantinB

    OBIEE to calculate the number of customers with 1 to 10 orders



      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 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?