Good day community, the BEx query output attached (screenshot 1), is as a result of the underlying BEx query formula (screenshot 2 attached) where the "Average per hour" column is equal to the value in the "Gross services" column divided by the value in the "Total Chargeable" column. The value highlighted in green (2,011) is expected, as this is 160,880 divided by 80. The value highlighted in red (3,516) is not expected, because 114,275 divided by 33 is equal to 3,462 and not 3,516. Please can you confirm why this incorrect value of 3,516 is displayed?
Hello,
I think this might be an issue because of decimal places. Can you please check the value by adding 2 decimal places for all the three columns in question and then see if you get the answer to your situation. Thanks.
Regards,
Shashank
Hi Dighe,
Thank you for your response.
I tried adding 2 decimal places as you mentioned and this unfortunately did not solve the problem.
So when you turned on the decimals, what values did you get? Did you check by doing manual calculations with the decimal values and see if you get the right values?
Regards,
Shashank
I get the decimal places when I turn on decimal places. I checked by doing manual calculations with the decimal values and still get the wrong values.
Regards,
Keith
Can you give an example of manual calculations. the other thing is to try using the whole function in your calculations.
Eg whole(kf1) / whole(kf2)
Hi,
Please follow the below checks
1) Make sure that correct columns are used in the formula. Some times it can be confusing when you are using same column name and you end up having wrong column selected in the formula.
2) There can be formula collision which might create this issue. In the Extended tab in the Eliminate formula Collision select use Result of this formula
3) Make sure that you are not using any exception aggreggation or any selection in the Calculation tab.
Regards
Gajesh
Hi Gajesh,
Thank you for your communication.
I have gone through steps 1, 2 and 3 as you have mentioned (checking to make sure) and unfortunately the situation remains exactly the same.
Please can you advise on the way forward in light of this?
Regards,
Keith
Hi,
Very strange. Can you create the query afresh and check or try in different system.
Regards
Gajesh
Hi,
Very strange. Can you create the query afresh and check or try in different system.
Regards
Gajesh
Hi.
I tried creating the query afresh and still receive the same result unfortunately.
Regards,
Keith
hi,
How are those kf defined (some kind of exc aggreg?)?
you could try something like
SUMCT (Total Gross) / SUMCT (Total Hours)
Grtz
Koen
Hi Koen,
Thank you for your contribution.
I defined the query with "SUMCT" as you mentioned and this unfortunately did not solve the problem.
Exception Aggregation on InfoObject level is "Summation."
Exception Aggregation on query level is "Use Standard Aggregation".
for both KF ?
Yes, for both key figures Koen
Hi,
Are you using CELLS? Because you can't have correct and incorrect values at the same in a single column. Maybe you have CELLS and there is a different formula for that specific cell which gives you incorrect value.
OR maybe some details about the query is missing so members can't give correct advice on how you can solve your problem? Like if you did something special or settings something to your query.
Regards,
Loed
Thank you for the contribution Loed, but I am not using CELLS.
Hi,
Then you may give us additional background about the queries on what has been changed or done on it so we may be able to help you.
But if recreating the query still gives you the same result, then my guess is in the elements of the formula used. You can't have correct and incorrect values at the same time in a single column. You may recreate the query by just having the elements needed for the formula to work to check if it's really giving you incorrect values.
Regards,
Loed