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
OBI EE group by problem
Hi All
I am facing a bit of a roadblock with grouping and don't have the best SQL skills/OBIEE experience to do it.
CURRENT RESULT
| Opportunity number | MIN(Date of sale) | MAX(Date of realization) | Business Line Items | Revenue |
| 34562BIS | 8-5-2016 | 8-5-2017 | X | 5000 |
| 8-5-2016 | 8-9-2016 | Y | 4000 | |
| 8-5-2016 | 8-10-2016 | Z | 3000 | |
| 78652BIS | 9-11-2016 | 9-11-2017 | Y | 1000 |
| 17-5-2016 | 17-9-2018 | Z | 3000 |
I have an analysis for which I'm viewing as a pivot table currently. I have Opportunities (indicated by Opportunity number) which contain multiple 'Business line items' and corresponding revenues.
The business user wants this information condensed and only wants to see one record for each opportunity number. (Like in Desired result)
This record should select the business line item that has the maximum delta between max data and min date but in the revenue field - it should sum up the revenues for the entire opportunity.
DESIRED RESULT
| Opportunity number | MIN(Date of Sale) | MAX(Date of relization) | Business Line Items | Revenue |
| 34562 | 8-5-2016 | 8-5-2017 | X | 12000 |
| 78652 | 9-11-2016 | 9-11-2017 | Z | 4000 |
It is a bit contorted and i am not able to implement this. could you please help.
Thanks a ton!
Answers
-
1 - You need a level-based measure for Revenue pinned at the Opportunity dimensional hierarchy level.
2 - You need a ranking measure so you can select the appropriate delta row
Both of the above should be built in the RPD.
0