BAQ Calculated Field Error when value is negative

I have a Calculated field in a BAQ with the below expression to calculate the percentage.

(EstLaborHrs - ActLaborHrs) / (EstLaborHrs) * 100

It works fine if the result of (EstLaborHrs - ActLaborHrs) is a positive number but when it is negative, I am getting an error when I run the BAQ. See examples below.

What am I doing wrong? I can get this to work in Excel. I have tried various things I found in this user group and on other sites.

Can you share a screenshot of your actual calculated field (plus its metadata)?


EstLaborHrs = 1.70
ActLaborHrs = 270.12

I have tried various ways to structure the Case statement. If I remove the division and only include else (EstLaborHrs - ActLaborHrs) the BAQ returns the negative value. It does not seem to like when I try to divide.

Most likely when EstLaborHrs is equal to 0 otherwise the Est-Act equal0 will be 0 anyways.
Case statement might want to be:

(case when EstLaborHrs = 0 then -100 else ( 100 * ( EstLaborHrs - ActLaborHrs) / EstLaborHrs ) )
2 Likes

THANK YOU! This worked with one exception - it was missing the “end” statement. We also changed to use -0 instead of -100. Below is the final expression we used.
(case when EstLaborHrs = 0 then -0
else ( 100 * ( EstLaborHrs - ActLaborHrs) / EstLaborHrs )
end)

One thing we found strange was if we switch to use “Case when ActLaborHrs = 0…” instead of EstLaborHrs = 0…we received the Bad SQL statement error. Not sure why it works with EstLaborHrs and not ActLaborHrs. We do have scenarios where the EstLaborHrs = 0 but there is ActLaborHrs.

So sorry about that I did miss the ‘end’.

The bad sql is most likely when EstLaborHrs = 0 and causing a divide by zero error.

Yes, quick test confirmed it:


1 Like