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

### 5 Answers

• Best Answer 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
• Kim Plut Anshu Lilhori

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

• 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
• 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