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
How to create Percent of total in OBIEE
Hi
I have a requirement to create percent of total for one of the column.
I have created couple of logical columns from Order Date and Ship Date in RPD i.e.,
1. Days to Ship Actual - TimeStampDiff(SQL_TSI_DAY, "ORDERS"."ORDER_DATE" , "ORDERS"."SHIP_DATE" ) // Which gives No of Days information
2. Days to Ship scheduled - Which gives information about what to happen if there is a match in no. of days. Ship mode column contains data like Same Day, First class, etc. which is given below in the case statement
CASE "ORDERS"."SHIP_MODE"
WHEN 'Same Day' THEN 0
WHEN 'First Class' THEN 1
WHEN 'Second Class' THEN 3
WHEN 'Standard Class' THEN 6
END
3. Ship Status - Gives information about what to display in regard the ship status
if [Days to Ship Actual]> [Days to Ship Scheduled] then "Shipped Late"
elseIF [Days to Ship Actual]= [Days to Ship Scheduled] then "Shipped On Time"
Else "Shipped Early" end
Now the requirement is I want to calculate the percentage of total of Ship status column
Means I wanted to know what is the Contribution of each status like
What is the percentage of shipped Late, Shipped early and On time? in a single column.
I have used the below case statement for the requirement
CASE "D1_ORDERS"."Ship Status" WHEN 'Shipped Early' THEN ((1.0*SUM(CASE "D1_ORDERS"."Ship Status" WHEN 'Shipped Early' THEN 1 ELSE 0 END))/(1.0*COUNT("D1_ORDERS"."Ship Status")))*100 WHEN 'Shipped Late' THEN ((1.0*SUM(CASE "D1_ORDERS"."Ship Status" WHEN 'Shipped Late' THEN 1 ELSE 0 END))/(1.0*COUNT("D1_ORDERS"."Ship Status")))*100 WHEN 'Shipped on time' THEN ((1.0*SUM(CASE "D1_ORDERS"."Ship Status" WHEN 'Shipped on time' THEN 1 ELSE 0 END))/(1.0*COUNT("D1_ORDERS"."Ship Status")))*100 ELSE 0 END
But the problem with this is when I add Ship status dimension column in the table everything coming as 100%
With out dimension column it works perfect as expected
I am trying out to figure out this, but unable to achieve it. Please help me to fix the requirement.
Thank you
Answers
-
I find % calculations, divisors and case logic etc are 'tricky' when done in the front end, but something that often helps is to change the setting to server complex aggregate, forcing the solution to calculate it in the 'backend' not on the client.
My preference though is to create measures in the rpd for these kind of calculations then you can pin the elements of the calculation to the hierarchical levels that you want them to reflect.
i.e. Create a measure for your Total and copy it into the level of a dimension hierarchy at which you want it to total.
Create your logic for shipped late as a measure and as a physical calculation.
Create a measure which Divides the second measure by the first.
Hope this helps?
0 -
Hi Robert Angel
Thanks for your reply and suggestion.
As I mentioned in the post above all these calculations I have created in RPD only not in front end.
And am not clear with this point
i.e. Create a measure for your Total and copy it into the level of a dimension hierarchy at which you want it to total.
Create your logic for shipped late as a measure and as a physical calculation. -- And I have tried this but of no use.
0 -
Hi Sandesh,
my points are; -
Creating your measure which just gives 1 for shipped late, 0 otherwise (simplified) as a PHYSICAL calculation will force OBIEE to do the logic FIRST and aggregate second. This is one of the crucial factors.
Create your measure which counts pinning it at the total level on the dimension hierarchy that you want it to be computed - i.e. I am not clear on your requirement but if it is a count by ship mode then you need in the order dimension hierarchy a level by ship mode and you need to copy your measure onto that level to ensure that that is always its level of granularity. If you want the total by sum of ship mode then you need to pin the measure at the level above the dimension hierarchy level that your ship mode varies. (Or any other dimension attribute that you want the total to be invariate for)
You then create a third measure that uses the 1st measure above and the second measure above to give your % total.
Hope this clarifies?
0 -
You can duplicate the measure column in the pivot to make it % of totals
0 -
Hi asim cholas,
Thanks for your reply
It will certainly help me to do the requirement. Can we do the same the graphs as well.
I will update here shortly. Thanks once again
0 -
Dear Sandesh,
You can create a graph from pivot itself and hide the pivot if you dont need it. Something like below.
0 -
Hi asim cholas,
I appreciate the time you spent to answer my question.
Other than Pivot. Is there anyway I can represent this in Bar chart?
For your reference I have the screenshot of the same
0 -
Will try and update you. Could you please mark helpful answers? If you have to create in the same way, you will have to create 3 measures instead of one
0 -
Yeah, Earlier I have created the three different columns for this requirement. But that doesn't served my purpose of single column bar chart. So I was trying out is there any other way I can achieve this.
Certainly asim cholas& Robert Angel helped me in this.
asim chola, As you said if you got to know any resolution to this please do reply.
Thank you guys.
0 -
Check the below format
For % either change it in data format or multiply with 100 which will be better for Graph Scales.
Create three measures one for early , late then on time. Column formula like below.
(sum(case when "Order_status"."ship mode E"="Order_status"."Ac" then 1 else 0 end))/(count(distinct "Order_status"."order_id"))
set aggregation rule as average and try.
In the chart itself.
Thanks
0