Rounding the values in Query designer

[former_member1119119]

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

[Former Member · Accepted solution]

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

[former_member188080]

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

[Former Member]

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

[Former Member]

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.

[anshu_lilhori]

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

[former_member1119119]

HI Al -

Your suggestion:

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(A5100-TRUNC(A5100)<0.5,FLOOR(A5100,1)/100,CEILING(A5100,1)/100

)

is exactly what I need, I am struggling to find the formula to round the key figure to 1 decimal in the query, any suggestions on how to modify the above formulas to return one decimal. I was able to successfully test in Excel but cannot get the correct formula in Query designer.

Thanks

Kim

[anshu_lilhori]

Hi,

I dont have system to test i think you may try the second formula the one with two decimal places and instead of 100 use 10.

Try it out and see whether it works or not.

Regards,

AL

[former_member1119119]

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