Question:
Hi everyone,
I am having a peculiar problem in a BEx query. Its a simple target and actual figure with a calculated key figure as the diff bet actual and target. for their totals i have set the calculate result as average. It gives the desired result, but the subtotal picks up a completely different value. for instance, if the data is as below
.........................Actual........Target....Diff
value a................10..............8...........2
value b................12...............8...........4
subtotal.................11..............8............3
instead of the desired subtotal diff "3", the query gives me a completely different negative value. I have tried setting the Calculate result as Nothing defined but still no luck.
Can anyone help...........
Thanx and regards,
Zubin
Answer:
I am having a peculiar problem in a BEx query. Its a simple target and actual figure with a calculated key figure as the diff bet actual and target. for their totals i have set the calculate result as average. It gives the desired result, but the subtotal picks up a completely different value.
----------------------------------------------------------------------------------
It is possible that what we have here is a 'collision' of formulas.
The last entry in your table can be calculated as a difference and also as an average, even though the result is the same.
Look for a checkbox somewhere in the definition of the calculated KF that asks you which formula should take precedence in case of a collision.
Answer:
Dear Ara,
Thanx for your reply, but no luck with this either. I put the checkbox to both settings, resulting from this formula and then result from competing formula, but the error is still the same. This error result is minus 360 times the correct value which really beats me how the system calculates this figure. Is there any way I can know how the system arrives at this formula.
I then introduced a formula for std deviation for the subtotal to check if there is a competing formula but then despite the formula collision options the result is calculated as the std deviation. So I concur that there is not formula collision either.
Any suggestions or help, tips or pointers, clues would be highly appreciated.
Thanx and regards,
Zubin
Answer:
Hi Everone,
I managed to find out the logic of calculation by the system. For the actuals, the query calculates the correct subtotal while for the target figure the query calculates the subtotal as a summation although it displays it as average.
What I am looking for now is the query to display the average and calculate on the average value as well.
Any thoughts ???
Thanx and regards,
Zubin
Answer:
Hi Zubin,
It looks like a similar problem posted in this Forum: 'Calculation using Calculated Key Figure'
You see a result in a cell but the underlying value in the cell is a different number! You can produce this kind of effect in Excel, for example with formatting.
It is disturbing that it happens in BW. You can display a result but cannot do further calculations with it.
I have no other clue except VBA.