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)?
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 ) )
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.
