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
If your actual hours are 0, that will cause a division by 0 and that may be your error.
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.
Check for a null before you do the calculation was my suggestion too using a Case statement.
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 )
This is what I executed, since I’ve only just learned case command within this past hour, and it works.
when Act = ‘0’ then ‘0’
else ((Act - Est) / Act ) * 100
This also works as expected. Thanks everyone.