Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 214 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
AVG with DISTINCT on other column
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
|
| STATUS_DATE | ||
|---|---|---|---|---|
| 1 | 90 | 01/01/2019 | ||
| 1 | 91 |
| ||
| 1 | 92 |
| ||
| 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
Answers
-
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.
0 -
Cost never changes.
Can you please explain the second part? I didn't understand what I can do.
Thanks,
Itai
0 -
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
0 -
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)"
0