Oracle Transactional Business Intelligence

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

Dividing Metrics

201
Views
12
Comments

Summary

I am trying to divide two metrics -- Not working!!

Content

I am trying to divide two metrics and am not getting the results I need. Can someone help? 

The number of Won Opportunities is 19. The number of Closed is 56. I want to show 19/56 = 34%. The two formulas I've tried in the tool are below with the result I'm getting. 

FILTER ("Pipeline Facts"."# of Opportunities" USING ("Opportunity"."Status Name" = 'Won'))/FILTER("Pipeline Facts"."# of Opportunities" USING ("Opportunity"."Status Name" = 'Closed')) 

Result = 0

FILTER("Pipeline Facts"."# of Opportunities" USING ("Opportunity"."Status Name" = 'Won'))/"Pipeline Facts"."# of Closed Opportunities" 

Result = 1

I've been working on this for hours trying to find different, creative ways to trick the system to do the math. I created my own "Won Opportunity" and "Closed Opportunity" metric, I've used the out of the box fact ones. I've tried COUNT, SUM, AVG, you name it. I've tried what I think are all the ways to get the Math to work out. 

Help! Any suggestions? 

Version

11.1.1.9.0

Tagged:
«1

Comments

  • You may read up on math, data types and the concept of integer divisions:

    http://mathworld.wolfram.com/IntegerDivision.html

  • slgott
    slgott Rank 4 - Community Specialist

    This was not very helpful. But thanks anyway. 

  • Ok, more specific then. If both numbers are integers and you divide them, the result is an integer. 19 / 54 = 0 Because the result of the same as doubles is 19.00 / 54.00 = 0.3518518518518519 And 0.3518518518518519 expressed as an integer is 0.

  • slgott
    slgott Rank 4 - Community Specialist

    I understand. I apologize. I was not clear in my response. I did move the decimal place out in the formatting and it is still not working. I tried this formula too. 

    TRUNCATE(FILTER("Pipeline Facts"."# of Opportunities" USING ("Opportunity"."Status Name" = 'Won')), 4)/TRUNCATE("Pipeline Facts"."# of Closed Opportunities", 4)

    Still just getting 1. Am I still misunderstanding?

    Thank you,

    Stephanie

  • Formatting isn't changing the data type. If I format an INT to show 4 decimal places it's still an INT. So 19 / 54 = 0.0000 However ( 19 * 1.00 ) / ( 54 * 1.00 ) = 0.351851... That's data type conversion by math. CAST( 19 as DOUBLE) / CAST( 54 AS DOUBLE ) = 0.351851... That's the explicit, programmatic data type conversion. A calculation engine works with what it gets. Not with what you think (your brain translates) gets interpreted. Math is math.

  • slgott
    slgott Rank 4 - Community Specialist

    This is what I just tried and it's not working

    TRUNCATE(FILTER("Pipeline Facts"."# of Opportunities" USING ("Opportunity"."Status Name" = 'Won')), 4)/TRUNCATE("Pipeline Facts"."# of Closed Opportunities", 4)

    I will try your above suggestion. 

    Thank you. 

  • Truncate has no impact on the data types involved in the maths that are happening.

  • slgott
    slgott Rank 4 - Community Specialist

    CAST("Win Loss Facts"."# of Won Opportunity Revenue Lines" AS DOUBLE) / CAST ("Pipeline Facts"."# of Closed Opportunities" AS DOUBLE)

    Still get 1. 

    I'm beginning to think it's something with the way the data is structured by Oracle. Is that possible? 

  • Not sure what to say. Here's how the tool works with numerical data types and conversions both programmatic and mathematic.

    31.01.png

  • Jonathan Chan gmg
    Jonathan Chan gmg Rank 5 - Community Champion

    Thanks for posting the clear illustration.