Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Custom Average Calculation in Report
I have a report in which i have to calculate average for a specific base criteria.
e.g
Location of Action | Action Performer | Count of Actions Performed
US| Employee 1 | 5
US| Employee 2 | 10
US| Employee 3 | 15
================
Total: 3 | 30
My report is much more detailed (flat) but i am displaying only performer level details even when i have details per performer action level details too.
Average of Action/Performer = (5+10+15)/3 = 10
I need to add additional column to this report for comparative analysis as follows
Location of Action | Action Performer | Count of Actions Performed| National Average of Activities
US| Employee 1 | 5 | 10
US| Employee 2 | 10| 10
US| Employee 3 | 15| 10
================
Total: 3 | 30
i have tried using the following formula's but non seem to give me what i expect
AVG
SUM( Count of Actions Performed) / Count Distinct (Action Performer)
But in all the expressions the issue seems to be with denominator's.
I am able to get SUM( Count of Actions Performed) as 30.
But when I calculate Count Distinct (Action Performer) it gives me for each row count as 1 not total table count of 3.
How do i achieve this ?
Answers
-
You must tell OBIEE that you would like the COUNT DISTINCT not at the row level but at the "US" level.
So you can try by using the AGGREGATE(... AT ....) function if you have a hierarchy containing your "Action Performer" and its upper level, or try by adding a : by "The column name of the column with the US value"
In this way you move the COUNT DISTINCT to the upper level and it will give you 3.
0
