Categories
- All Categories
- 135 Oracle Analytics News
- 24 Oracle Analytics Videos
- 14.6K Oracle Analytics Forums
- 5.6K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 51 Oracle Analytics Trainings
- 9 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 3 Oracle Analytics Industry
- Find Partners
- For Partners
Reg: Group report requirement
Hi Team,
We have 3 columns region ,duration and units duration is from ( 1 Hrs to 24 Hrs ) and our requirement is we need to group by duration from 8 Hr to till 12 Hr based on units ( like >= 8 hr, >= 9 hr , >= 10 hr , >= 11 hr ,>= 12 hr final report should be like below can any one suggest on this.
Version: OBIEE 11.1.1.7
| Region | Time | Units |
| ABC | >=8 ( it as to add 8+9+10+11+12) | 100(Sample Value) |
| ABC | >=9 (it as to add 9+10+11+12) | 80(Sample Value) |
| ABC | >=10 (it as to add 10+11+12) | 70(Sample Value) |
| ABC | >=11 (it as to add 11+12) | 60(Sample Value) |
| ABC | >=12 ( it as to sum remaining sum) | 1000(Sample Value) |
Thanks in Advance,
Answers
-
to just group durations you can use bins.
0 -
Not sure BINs will do the trick here as he wants to have a) overlapping ranges (which bins could do) and b) wants to have the numbers aggregate with double-counting (multi-counting even) inside the aggregation.
So basically the categorization has to happen pre-aggregation whereas BINs kick in post-aggregation.
(sorry...mobile phone posting joys)
The BIN would have to be based on a formula which forces the lowest level of aggregation (the grain) and then the analysis would have to reconstruct it again and aggregate. Maybe better to push that to the RPD in the first place and nicely model it as a fact-derived attribute in a degenerate dimension.
0 -
Hi @Raa.BI ,
you can create a simple report using REGION , TİME and UNITS colums (I assume that UNITS column is a measure that have SUM aggregation on rpd level). Then use the formula like this for UNITS column
CASE WHEN ("TIME")=8 THEN SUM(FILTER("Units" USING ("TIME")>=8) BY "REGION")
WHEN ("TIME")=9 THEN SUM(FILTER("Units" USING ("TIME")>=9) BY "REGION")
WHEN ("TIME")=10 THEN SUM(FILTER("Units" USING ("TIME")>=10) BY "REGION")
WHEN ("TIME")=11 THEN SUM(FILTER("Units" USING ("TIME")>=11) BY "REGION")
WHEN ("TIME")=12 THEN SUM(FILTER("Units" USING ("TIME")>=12) BY "REGION")
END
0 -
Yes you are right, didn't think of it. So in that case another option would be to create a union report, not the cleanest of ideas.
0 -
But a valid one ;-)
What @3068122 posted is an option which represents one approach but then he'd still need an attribute to split the result set by, representing those tranches of data since the result here is a an aggregated measure.
0 -
Hellow All,
Apologies for the delay reply. Thanks for your inputs.
I have achieved the above requirement using combined similar request. I have created 4 criteria's and applied all my filters\logic for time column at each level.
Thanks ,
0 -
Hi @Raa.BI,
Glad that one of our suggestions worked for you.
0