AVG with DISTINCT on other column

Question

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_IDCOST110220

ORDER_STATUS

FK_ORDER_ID

STATUS

STATUS_DATE19001/01/201919102/01/2019

19201/01/2020

29001/01/201929101/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

SonPat99 · Answer

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)"

Thomas Dodds · Answer

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

Itai_s · Answer

Cost never changes.

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

Thanks,

Itai