Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Odd behavior on OBIEE subtotals

Received Response
1
Views
2
Comments
Dennis Hancy
Dennis Hancy Rank 5 - Community Champion

I have this column on an analysis.  The underlying formula being used is

CASE  WHEN ."Value1" >0

THEN "Value1"

ELSE "Value2"

END

Basically I have two numeric columns for each record.  I want to report whichever one is greater than 0.

The detail of the report is correct.  I have three lines with these values; 2, 5, 2.  The first "2" is from Value1; the "5" and other "2" are from Value2.

For this value column, I selected this aggregation rule: Default and Report-Based Total (when applicable).

The problem comes on the subtotals.  On the results tab, I selected to add Totals after each change in a date field.  When it prints the total for this date, It shows "7" (It should be 9: 2+5+2).

Any ideas why it's displaying the total incorrectly?

Thanks!

Dennis

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Hi Dennis,

    the problem comes in that OBIEE tries to aggregate on the highest level, and I have found that often with case statements this results in spurious results.

    Two suggestions; -

    Best - push your cash logic back into an rpd measure, make sure it is a physical calculation

    Might work - use the server complex aggregate option - it sometimes 'does the trick', but cf above, that is a better way to go

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

    Late to the party but as Robert said: this is something better left to the BI Server and not the analysis itself.

    Push back to RPD for proper control of what happens when.

    Keep in mind: pre-aggretation calculations will not yield the same results as post-aggregation calculations!