Skip to Content

Rounding the values in Query designer

In Query Designer, I am trying to store the rounded value of the below calculations to use in calculating a difference amount and %variance.

Currently:

Report shows actual % of 1.1 and budgeted % of 1.1, because we are rounding the actual value of 1.0798 to 1.1 and the budgeted value of 1.1326 to 1.1. The difference is calculated as budget u2013 actual or 1.1326 u2013 1.0798 = .0528 and the percent variance is 0.0528/1.1326 = 4.66%.

Requested:

The end user wants the rounded values of 1.1 used in calculating the differences and percent variance, so 1.1 u2013 1.1 = 0 difference and percent variance is 0/1.1 = 0%.

What function can I use to recall the rounded values to use in difference and percent variance calculations?

Thanks for any and all help!

Kim Plut

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 20, 2011 at 05:13 AM

    Hi,

    Create two CKFs - Budget and Actual and set the Properties of the key figures in BEx. Go to Calculations > Local Calculations > Calculate Results As... > Summation of Rounded Value.

    Use these CKFs in further calculations. Hope this would resolve your issue.

    Regards,

    Geeta

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 20, 2011 at 07:18 AM

    Hi,

    Please try out these formulas:

    If you want to round a key figure "KF" to have zero decimals:
    ( FRAC ( 'KF' ) < 0.5 ) * FLOOR ( 'KF' ) + ( FRAC ( 'KF') >= 0.5 ) * CEIL ('KF')
    
    An equivalent MS Excel formula you can use to test is:
    =IF(A3-TRUNC(A3)<0.5,FLOOR(A3,1),CEILING(A3,1))
    
    If you want to round a key figure "KF" to have 2 decimals:
    ( FRAC ( 'KF'*100 ) < 0.5 ) * FLOOR ( 'KF' * 100 ) / 100 + ( FRAC ( 'KF'
    * 100 ) >= 0.5 ) * CEIL ('KF' * 100) / 100
    
    The equivalent MS Excel formula to test it is:
    =IF(A5*100-TRUNC(A5*100)<0.5,FLOOR(A5*100,1)/100,CEILING(A5*100,1)/100
    )

    Hope it helps.

    Regards,

    AL

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks Al!

      That revised formula is working for all my actual values. Maybe you can help with my next issue, I have multiple columns in this query, three columns for actual and three columns for budget. So far I have been successful in adding the formula in the actual column, but when I try to add the formula in for the budgeted values (which are carried over by cell references), I am not getting any value returned, I actually get 0.

      Any thoughts on how to make the formula work in cell referenced columns?

      Any help is greatly appreciated!

      Thanks

      Kim

  • avatar image
    Former Member
    Oct 20, 2011 at 04:46 PM

    AL's solution will surely work. I have tried to use the Floor, Ceil, and Fraction on similar situation but different logic. Give it a try, it must work.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 20, 2011 at 04:50 PM

    Try this link and look for the sample for floor, ceil and frac. by this, you will easily understand the process that takes place on each operator and you will easily formulate different logic by using those three in different situations on decimal places.

    http://en.wikipedia.org/wiki/Floor_and_ceiling_functions

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 25, 2011 at 12:24 PM

    Hi,

    there two options

    1. carry out KF rounding . But in your case calculation is there,it will not correctly there

    2. You only need to go either for ABAP code or use formula as suggested in other posts.

    Thanks and regards

    Add comment
    10|10000 characters needed characters exceeded