Skip to Content
0

BEx query calculation yielding unexpected result field value for certain records

Sep 25, 2017 at 08:32 PM

96

avatar image
Former Member

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?

screenshot-1.jpg (62.9 kB)
screenshot-2.jpg (92.7 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

avatar image
Former Member Sep 27, 2017 at 01:44 PM
0

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Dighe,

Thank you for your response.

I tried adding 2 decimal places as you mentioned and this unfortunately did not solve the problem.

0
Former Member
Former Member

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

0
Former Member

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

0
Former Member
Former Member

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)

0
Gajesh Nagesh Sep 26, 2017 at 06:47 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

0
Gajesh Nagesh Sep 27, 2017 at 11:35 AM
0

Hi,

Very strange. Can you create the query afresh and check or try in different system.

Regards

Gajesh

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member
Hi Gajesh. I recreated the query as you mentioned and achieved the same result. Please can you advise further?
0
Gajesh Nagesh Sep 27, 2017 at 11:35 AM
0

Hi,

Very strange. Can you create the query afresh and check or try in different system.

Regards

Gajesh

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi.

I tried creating the query afresh and still receive the same result unfortunately.

Regards,

Keith

0
Koen Hesters Oct 02, 2017 at 07:38 AM
0

hi,

How are those kf defined (some kind of exc aggreg?)?

you could try something like

SUMCT (Total Gross) / SUMCT (Total Hours)

Grtz

Koen

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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".

0

for both KF ?

0
Former Member

Yes, for both key figures Koen

0
Loed Despuig Oct 03, 2017 at 06:07 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you for the contribution Loed, but I am not using CELLS.

0

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

0