Wednesday, December 28, 2011

OBIEE 11g: Calculated % always shows up as 0%

While upgrading some of my dashboards from OBIEE 10.1.3.4 to 11.1.1.5, I noticed that some of my calculations in Answers were showing zeros instead of the calculated amount.  The calculation I was trying to perform was to calculate a match rate % for some data based on a field that showed 'MATCH' or 'NO MATCH'.  The original calculation was:

(SUM(CASE BuyPoint.Match WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(BuyPoint.Match ))*100

However, this was returning 0% as the result.  This happens when you have an integer in a calculation.  A very easy fix to this is to multiply by 1.0 (don't use 1, as this will not work.  Use 1.0).  The updated calculation below works and provides a meaningful result.

((1.0*SUM(CASE BuyPoint.Match WHEN 'MATCH' THEN 1 ELSE 0 END))/(1.0*COUNT(BuyPoint.Match )))*100


Posted by at

9 comments:

  1. JeremyMay 10, 2012 at 11:25 AM

    DMurph,

    Will you be at the RittmanMead forum next week in the ATL? If so, hope to meet you then! Great blog man. Keep up the good work and many thanks!

    Jeremy

    ReplyDelete
    Replies
    1. DMurphMay 10, 2012 at 1:33 PM

      Thank you, Jeremy. I won't be at the Rittman Mead forum next week, although I did attend it last year. It was well worth the time. I have a conflict with another conference I'm going to, so I can't make the Rittman Mead one. Otherwise, I would be there.

      Delete
  2. JeremyAugust 30, 2012 at 4:13 PM

    DMurph,

    I had forgotten to subscribe via email to replies on this post, and never saw this until now. Sorry we didn't get a chance to run into each other at the RM Forum (was terrific). I also was at the prior year forum as well -- wish we'd met then! I work over in Norcross, somewhere not too far from "atlantabi" -- wherever that may be in the greater ATL area. :)

    Any chance we'll both meet on the left coast at OOW in Oct? I'm giving a presentation on BI Publisher+OBIEE based financial reporting. Oh joy! LOL

    Thanks again for the "Act As" post. Worked brilliantly.

    If you don't mind, drop me an invite on LinkedIn sometime: http://www.linkedin.com/pub/jeremy-harms/2/659/25a

    Later.

    ReplyDelete
  3. AnonymousAugust 27, 2013 at 1:25 PM

    Ran into this issue when converting. Seems like Oracle decided to assume/force integer division in the 11g calculation engine. We had to visually inspect every report for forced integer calculations. Very Disappointing.

    ReplyDelete
  4. AnonymousJanuary 30, 2015 at 5:13 AM

    This works great! Thank you so much! very helpful

    ReplyDelete
  5. AnonymousDecember 30, 2015 at 12:45 PM

    Thanks, DMurph.
    I had the same problem and your tip worked out just fine.

    ReplyDelete
  6. PreethamApril 11, 2016 at 8:11 AM

    Perfect. Worked for me.

    ReplyDelete
  7. UnknownApril 18, 2016 at 8:07 AM

    Hi Dmurph,

    Thank you very much for this advice. It helped me fix a problem :)

    I have over 7 years of experience in BI and ETL tools. In order to enhance my learning and share my knowledge, I would like to connect with you on linked In.

    Please send me an invite.

    https://www.linkedin.com/in/vipul-panchal-0495a125?trk=nav_responsive_tab_profile

    ReplyDelete
  8. Scott SMay 6, 2016 at 9:01 AM

    This solved my problem. Awesome find!

    ReplyDelete

Subscribe to: Post Comments (Atom)