# BAQ Calculated Field Error when value is negative

**URL:** <https://www.epiusers.help/t/baq-calculated-field-error-when-value-is-negative/79449>\
**Category:** Epicor ERP 10\
**Tags:** baq\
**Created:** [June 9, 2021, 3:24pm UTC](https://www.epiusers.help/t/baq-calculated-field-error-when-value-is-negative/79449 "2021-06-09T15:24:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![dgross](https://www.epiusers.help/letter_avatar_proxy/v4/letter/d/9fc29f/32.png) [@dgross](https://www.epiusers.help/u/dgross)\
**Post date:** [June 9, 2021, 3:24pm UTC](https://www.epiusers.help/t/baq-calculated-field-error-when-value-is-negative/79449/1 "2021-06-09T15:24:43Z")

</div>

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.

 ![image](https://www.epiusers.help/uploads/default/original/3X/e/0/e03d46d074d049327216339f7b41bcbd11d98bca.png)

---

<div class="post-metadata">

**Author:** ![Aaron\_Moreng](https://www.epiusers.help/user_avatar/www.epiusers.help/aaron_moreng/32/745_2.png) [@Aaron\_Moreng](https://www.epiusers.help/u/Aaron_Moreng)\
**Post date:** [June 9, 2021, 3:37pm UTC](https://www.epiusers.help/t/baq-calculated-field-error-when-value-is-negative/79449/2 "2021-06-09T15:37:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dgross](https://www.epiusers.help/letter_avatar_proxy/v4/letter/d/9fc29f/32.png) [@dgross](https://www.epiusers.help/u/dgross)\
**Post date:** [June 9, 2021, 3:49pm UTC](https://www.epiusers.help/t/baq-calculated-field-error-when-value-is-negative/79449/3 "2021-06-09T15:49:26Z")

</div>

![image](https://www.epiusers.help/uploads/default/original/3X/6/5/65d5135ec189ca7efe6e358bf23ab50b79911162.png)  
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.

---

<div class="post-metadata">

**Author:** ![CSmith](https://www.epiusers.help/user_avatar/www.epiusers.help/csmith/32/50752_2.png) [@CSmith](https://www.epiusers.help/u/CSmith)\
**Post date:** [June 9, 2021, 4:50pm UTC](https://www.epiusers.help/t/baq-calculated-field-error-when-value-is-negative/79449/4 "2021-06-09T16:50:52Z")

</div>

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

```auto
(case when EstLaborHrs = 0 then -100 else ( 100 * ( EstLaborHrs - ActLaborHrs) / EstLaborHrs ) )

```

---

<div class="post-metadata">

**Author:** ![dgross](https://www.epiusers.help/letter_avatar_proxy/v4/letter/d/9fc29f/32.png) [@dgross](https://www.epiusers.help/u/dgross)\
**Post date:** [June 9, 2021, 9:00pm UTC](https://www.epiusers.help/t/baq-calculated-field-error-when-value-is-negative/79449/5 "2021-06-09T21:00:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![CSmith](https://www.epiusers.help/user_avatar/www.epiusers.help/csmith/32/50752_2.png) [@CSmith](https://www.epiusers.help/u/CSmith)\
**Post date:** [June 9, 2021, 9:22pm UTC](https://www.epiusers.help/t/baq-calculated-field-error-when-value-is-negative/79449/6 "2021-06-09T21:22:21Z")

</div>

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:

 ![image](https://www.epiusers.help/uploads/default/original/3X/4/1/410bd20a724b67fe0ad64a258b832df44cdf9312.png)  
 ![image](https://www.epiusers.help/uploads/default/original/3X/b/1/b1532d4a7510adb778aedc2f0dfd37b61539ff65.png)