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

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?

• ###### 1. Re: OBIEE to calculate the number of customers with 1 to 10 orders

Provide the table details with columns?

• ###### 2. Re: OBIEE to calculate the number of customers with 1 to 10 orders

Here it is:

• ###### 3. Re: OBIEE to calculate the number of customers with 1 to 10 orders

can you try below formula and let me know.

sum(case when count(distinct order_id by cust_id,cust_country) between 1 and 10 then 1 else 0 end)

if you are using order quantity, you can use sum(order_quantity).

Thanks

Aj

• ###### 4. Re: OBIEE to calculate the number of customers with 1 to 10 orders

In my answer, I get one number for each country.

seems like not grouped by cust_id or what ?

• ###### 5. Re: OBIEE to calculate the number of customers with 1 to 10 orders

sum(case when count(distinct order_id ) between 1 and 10 then 1 else 0 end by cust_id,cust_country)

include the cust_id in report and exclude it from results.

add a group by clause at the end and see

• ###### 6. Re: OBIEE to calculate the number of customers with 1 to 10 orders

Wow, Including/Excluding cust_id, do the magic:

Without this, I've got only ones and zeros:

I suppose it changes the grouping of the aggregation. Thanks,