Skip to Content
avatar image
Former Member

BEx query calculation yielding unexpected result field value for certain records

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

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

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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)

  • Sep 26, 2017 at 06:47 AM

    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

    Add comment
    10|10000 characters needed 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

  • Sep 27, 2017 at 11:35 AM

    Hi,

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

    Regards

    Gajesh

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Hi Gajesh. I recreated the query as you mentioned and achieved the same result. Please can you advise further?
  • Sep 27, 2017 at 11:35 AM

    Hi,

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

    Regards

    Gajesh

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi.

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

      Regards,

      Keith

  • Oct 02, 2017 at 07:38 AM

    hi,

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

    you could try something like

    SUMCT (Total Gross) / SUMCT (Total Hours)

    Grtz

    Koen

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 03, 2017 at 06:07 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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