Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 215 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Divide 2 Columns in Union Report
Hi All,
Oracle Business Intelligence 12.2.1.1.0.
I am struggling to create a calculated column which perform division on Union.
I need to create a calculated column to perform calculation Amt 1/ Amt 2.
So far result is correct. Now I am going to add a Region column to both analysis and then exclude it in the pivot table layout. When I do that, Division result is not correct.
How can I fix this issue ?
Thanks
Ebin
Answers
-
By looking at the query and your models. Including the Region changes the query grain!
Look at the two detailed queries. Look at how "Region" changes things. Look at what data arrives and whatt gets calculated.
This is analytics 101 - you have to get behind it and understand the impact of what you are doing.
0 -
If I understand correctly, you want to divide 2 measures derived from a union report. If so, you add a result column and use the syntax saw_0/saw_1 where the number indicates the position of the column in the analysis - where 0 = position 1 and 1 = position 2 etc
0 -
If you had the right answer with the region in can you make region hidden using the format properties to get the right result, or does that yield the wrong level of summarisation?
0 -
That's my point - it's a conceptual question which may point to insufficient or just completely wrongly done logical modeling.
0 -
Hi Joel,
I tried that already, but not getting the desired result.
0 -
Hiding the region results in wrong level.
0 -
Hi Christian,
Before posting in OTN, I verified the data part. Report total shows the same number with or without region. Only issue is while dividing 2 columns in union report. If I specify the aggregation rule as average for the "division column", then it calculates the average of the division for each record.
0 -
then you need to look at @Christian Berg's response in your thread. Check your model as you're adding in the Region dimension attribute but you don't want to aggregate at the Region level so I'm personally confused with what it is that you're trying to achieve.
0 -
Sorry for the confusion... We have a different view within same report where Region is used. For the pivot table view in question, it gives a summary and therefore region is excluded.
0 -
If you need a fast fix then you could use the physical sql generated in opaque view or direct database request.
For a longer term fix I would look at the fact to dimension levels set in the LTS
0
