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

KonstantinB · Jul 28, 2017 7:20AM

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?

asim cholas · Jul 30, 2017 9:13AM

Provide the table details with columns?

KonstantinB · Jul 31, 2017 2:58AM

Here it is:

asim cholas · Jul 31, 2017 3:43AM

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

KonstantinB · Jul 31, 2017 3:56AM

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

seems like not grouped by cust_id or what ?

asim cholas · Jul 31, 2017 4:32AM

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

KonstantinB · Jul 31, 2017 5:28AM

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, asim cholas

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

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

Answers