Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 214 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
obiee 12c calculated measure from two other measures - incorrect values
Hi Gurus,
we have a requirement to create a custom metric in answers. This new metric is the division of other two metrics.
Issue is when i create this new metric and pull the other 2 fields into the f(x) and set aggregation content to average , obiee shows incorrect values.
Third metric is a simple division of first metric (the numerator, its a double and a dollar amount field) and second metric (denominator, its an integer and a simple "no of orders" field).
I gave the correct aggregation rule, but showing wrong answers. I even tried "cast as double" and "avg" aggregation rule within the F(x) of new metric. Still no luck.
Not sure what went wrong. Also the first 2 metrics are calculated fields themselves with "sum(max( fact column by some dimensions )) " kind of calculation with their aggregation rule as sum.
can any one interpret this. appreciated. Thanks.
Sorry forget to mention, all the 3 metrics are being created in answers, not RPD. Unfortunately we don't have the option of moving this work to be done in RPD.
Answers
-
Simple answer, whenever you are looking at division the measure must be physical not logical.
So recreate a physical measure based on the physical not logical columns - it is down to the difference between aggregate first then divide OR divide first then aggregate.
0 -
Sorry Robert, did not get you. Physical means the first two metrics should be created in the rpd ? that is what you mean ?
0 -
Hi Robert, here i am creating all 3 metrics in answers, not in RPD. updated the original question.
0 -
Dear User,
Can you also give some sample values for all 4 columns (new calculated and existing aggregated columns)?
Also, a screen of what is getting out.
0 -
Hi, please see below values.
A, B, C are 3 metrics. C = A/B.
Below are values.
A (amt , double) B (no of items, integer) C (correct value) C (what i am getting)
$52.00 1 $52.00 $52.00 ---------> correct
$0.00 1 $0.00 $0.00 -----------> correct
$189.80 4 $47.45 $47.45 ----------> correct
$522.61 9 $58.06 $46.56 ---> WRONG
$779.20 10 $77.92 $85.80 --------> WRONG
$402.00 8 $50.25 $38.55 -----------> WRONG
0 -
Can you also provide the column formulae used for A, B and C?
0 -
Column A -- SUM(MAX(fact column by dim1) BY dim2, dim3)
column B -- SUM(MAX(fact column by dim1, dim2, dim3) BY dim3, dim2)
both A and B has aggregation rule as SUM
As said , C = A/B, C has aggregation rule as AVG
0 -
On your measures that are coming out incorrectly have you tried ticking 'server complex aggregate' for those measures?
0 -
Your result isn't "incorrect values". You are confusing post-aggregate calculations with pre-aggregate calculations.
As Robert said in the very first post - htis belongs into the RPD in a physically (nece row-by-row and pre-aggreagte) calculation in the logical layer. NOT a derived calc and NOT a front-end calc.
0 -
I know it is not an ideal solution, but I recommended the use of 'server complex aggregate' because it pushes the calculation back to the database, aggregation and all.
Like you standing on the high ground of good architectural practise I prefer measures in the rpd, but from past experience the sum by two dimensions functionality might rule this out - I know you can create measures pinned to dimensions at a certain level, but depending on how filtered the result is in answers you can struggle to do it all in the rpd.
I remember a dynamic share of market / share of segment calculation where the client wanted the afore mentioned market / segment population to be based on multiple runtime parameters when I could not get a pure rpd measure to do what was required.
0