Question:
Hi Reporting Gurus,
I am on ver 3.0B and have a reporting scenario, where I use scaling of a million.
When I use a formula on the scaled values rounded to say 2 decimal units, it doesn't internally use the scaled values but the actual values .
For example :
Actual = 0.02963874 , scaled value = 0.03
Budget = 0.032887 , scaled value = 0.03
Variance % using the formula I get is 10percent , while what I need is 0.
Any solution/workarounds ? I need it only at the query level, not at the cube/ODS level.
Thanks,
Harvinder
Answer:
maybe via calculated key figures?
Answer:
Hi El Belgio,
I am using calc key figure. But how am I going to achieve what I have asked for ? Is there any rounding function which can be used in calculations ?
Thanks,
Harvinder
Answer:
Hi, Harvinder
The behavior you're describing is normal, scaling is only for display.
To round the values, use the following formula :
Actual (without scaling) = 2963.874
Budget (w/o scaling) = 3288.7
Variance = NDIV0(TRUNC(Actual/1000 + 0.5) / TRUNC(Budget/1000 + 0.5) * 100)
Trunc is a Mathematical function, Integral value of the operand.
Actual/1000 = 2,963, so Trunc will retrieve 2, unless you apply the (+0.5).
If Actual/1000 gives a decimal part less than 0.5, the rounding will apply on the floor integral value. On the other side, if the decimal part is over 0.5, the rounding will retrieve the ceil integral value.
'Hope this helps.
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.
Answer:
Thanks Fred,
That's exactly what I was looking for !
Answer:
Hi there:
I'm having the same issue, and I tried to use Trunc too. The only problem is when the Key figure is negative it will add 0.5 giving you an incorrect rounding.
What do you do in this case?
Thank you for your help in advance.
Eladio
Answer:
make a test on the value you want to truncate, something like
TRUNC(value/1000 + 0.5*(Value >0) -0.5*(Value <0))
Be mathematic !
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.