BAQ Calculated Field for Percentage Change

Kinetic ERP
, ,
1

Wondering if it’s possible to do this. It’s for showing a percentage over/under labor hours.

The general formula is ( (Value2 - Value1) / Value1) * 100

I’m summing all Estimated and Actual labor with calculated fields.

Est: JobAsmbl.TLESetupHours + JobAsmbl.TLEProdHours + JobAsmbl.LLESetupHours + JobAsmbl.LLEProdHours
Act: JobAsmbl.TLASetupHours + JobAsmbl.TLAProdHours + JobAsmbl.LLASetupHours + JobAsmbl.LLAProdHours

In context it would look something like this, but this gives me a bad SQL statement error:
((Act - Est) / Act ) * 100

2

If your actual hours are 0, that will cause a division by 0 and that may be your error.

2 Likes
3

That is true, we’ve migrated data from a previous ERP system and a lot of older jobs don’t have estimated or actual labor hours in Kinetic. Do need to think of a workaround for that.

4

Check for a null before you do the calculation was my suggestion too using a Case statement.

1 Like
5

Try something like this.
Check if Act is Null or 0, if so return 0
else do your formula.

IIF( ISNULL ( Act , 0 ) = 0, 0 , ((Act - ISNULL ( Est , 0 )) / Act ) * 100 )

6

This is what I executed, since I’ve only just learned case command within this past hour, and it works.

(case
when Act = ‘0’ then ‘0’
else ((Act - Est) / Act ) * 100
end)

1 Like
7

Way to go!

8

This also works as expected. Thanks everyone.