AVG with DISTINCT on other column

Itai_s · Jan 31, 2019 9:30AM

Hi,

I am using OBIEE 12C.

I'm trying to accomplish the following scenario:

I have an orders table & order status table, they are connected with order_id.

In orders tables I have a COST column which I want to calculate its average.

Example (tables):

ORDERS

ORDER_ID	COST
1	10
2	20

ORDER_STATUS

FK_ORDER_ID

STATUS

STATUS_DATE

1

90

01/01/2019

1

91

02/01/2019

1

92

01/01/2020

2

90

01/01/2019

2

91

01/01/2020

So if I want to calculate AVG cost for all orders the answer will be 15 ((10+20)/2).

Now my problem is that if I want to filter my report to Average only orders from year =2019, OBIEE will create a JOIN between ORDERS and ORDER_STATUS tables.

This JOIN causes an incorrect average calculation: I get AVG = 13.33333. ((10*2) +20)/3

My question:

Can I somehow AVG only DISTINCT ORDER_IDs?

Thanks in advance,

Itai

Thomas Dodds · Jan 31, 2019 10:58AM

does cost change based on status? if not - you really want the average across orders but only for the most recent status ... then you have the concept of distinct without the shortcut of distinct.

Itai_s · Feb 3, 2019 2:33AM

Cost never changes.

Can you please explain the second part? I didn't understand what I can do.

Thanks,

Itai

Thomas Dodds · Feb 4, 2019 7:22AM

Since cost doesn't change you can set the aggregation on cost specific to status_date dimension as being LAST ... then the cost returned is only the most recent for any given order. Also set it to SUM for all other dimensions.

^ the above is predicated on you having modeled the data correctly to support the use

SonPat99 · Feb 11, 2019 1:21AM

Hello Itai_S,

OBIEE is not calculating incorrect AVG, as there are 2 Orders for OrderID 1 and 1 for OrderID 2, summing to 40 divided by Number of Orders (3 in this case).

Can you try to create a column with formulas as: "SUM(COST) / COUNT(DISTINCT ORDER_ID)"

Oracle Analytics Cloud and Server

Categories

AVG with DISTINCT on other column

Answers