BAQ Calculated field division

Kinetic ERP
,
1

I have a BAQ for evaluating job efficiency. I am attempting to divide a field by a calculated field, both decimals, but when analyzed there are zero rows returned. I have been unable to find a solution for this issue.

select 
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[LaborDtl].[OpCode] as [LaborDtl_OpCode],
	[JobHead].[ProdQty] as [JobHead_ProdQty],
	(LaborDtl.LaborQty + LaborDtl.ScrapQty) as [Calculated_TotalQty],
	[LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
	(TotalQty/ JobHead.ProdQty* LaborDtl.LaborHrs* 1.0) as [Calculated_Shouldhavetaken],
	[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
	**((LaborDtl.LaborHrs- Shouldhavetaken)/ Shouldhavetaken) as [Calculated_effpercent]**
from Erp.LaborDtl as LaborDtl
inner join Erp.JobOper as JobOper on 
	LaborDtl.Company = JobOper.Company
	and LaborDtl.JobNum = JobOper.JobNum
	and LaborDtl.AssemblySeq = JobOper.AssemblySeq
	and LaborDtl.OprSeq = JobOper.OprSeq
inner join Erp.JobOpDtl as JobOpDtl on 
	JobOper.Company = JobOpDtl.Company
	and JobOper.JobNum = JobOpDtl.JobNum
	and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
	and JobOper.OprSeq = JobOpDtl.OprSeq
inner join Erp.JobHead as JobHead on 
	JobOpDtl.Company = JobHead.Company
	and JobOpDtl.JobNum = JobHead.JobNum
where (LaborDtl.ClockInDate = dateadd (day, -1, Constants.Today))

image

Thanks

2

Try Calculated_shouldhavetakein instead

3

Also, if your “Shouldhavetaken” is ever = 0, then there will be an error. Either ensure your “shoulda” value is set to never = 0, or place a conditional to return something other than error if there is a 0 in there.

1 Like
4

Not sure what you mean.

I chose “shouldhavetaken” from the dropdown menu of calculated fields and I cannot directly edit the SQL on the “general” screen.

I attempted to change “Shouldhavetaken” to “Calculated_Shouldhavetaken” in the editor but it errors out.

5

good idea, i recalled it too Calculated Field
but i thought it is another error shown

1 Like
6

then I am wrong.
If you remove that calculation field, does query return something?

7

Look at the expression editor for the calculated field: Calculated_effpercent. You can edit the text in that expression editor to include the Caluclated_ prefix.

8

Tried that but when i check the syntax it says invalid column name.

I am trying to filter out the zero values. How do I add table criteria for a calculated field?

9

Yes:

image

54 rows of information

10

It is not just zeros, also if there is a null it will give an error. Olga’s link has information on using a condition to ensure you don’t get those divide by zero errors.

11

in subquery criteria

12

Yes, or no error, just doesn’t work as expected.
So I have gotten into the habit of checking for Nulls when I do division.
Maybe something like this for your BAQ?

(case
when ISNULL(ShouldHaveTaken,0) <> 0
and ISNULL(LaborDtl.LaborHrs, 0) <> 0
then ((LaborDtl.LaborHrs - ShouldHaveTaken)/ShouldHaveTaken)
else 0
end)

13

I am searching subquery criteria on the query builder screen but do not see either of my calculated fields.

14

Got it, I will check through the link Olga provided.

15

Do I add this text into the editor of the calculated field?

16

Yes, that entire case statement can be put in the expression editor for your calculated field.

1 Like
17

ISNULL should go into denominator, do not try filter using case, it won’t work

1 Like