BAQ Formulas may not be what they should be

Epicor ERP 10
,
1

I am working on a supplier performance dashboard that will show the percent on-time for a supplier based on due date. Epicor has a dashboard that is an executive dashboard, but it shows number of receipts and number of on time. The percentage on time should be straight forward…Not so fast - this is what I discovered and wanted to share how formulas may give different results.
It doesn’t have to make sense to work, but this solved the problem. A x 100 / B x 100 <> A/B

image
image.png1327×273 49.9 KB

Here’s the formula for the two calculated columns

image
image.png1201×508 24.3 KB

image
image.png1477×498 28.1 KB

2

If your initial fields are integers, you will lose decimal precision I believe. Make all your calc values decimal and it may solve this math.

1 Like
3

Without seeing the raw data, I’m guessing that decimal places are somehow involved?
Maybe…
Ontime:… roundng decimals up
Calc OnTime:… cutting decimals off
CalOnTime * 100:… catching decimals up to 3 places?

4

What is the data type of the Calculated_OnTime and Calculated_RcptCount?

5

They were both set to decimal.
I thought about that too.
The sum of number of receipts and number of on-time receipts is an integer.
I tried several different ways, if I divided by 1,0000 so I could show it as a percentage, it also returns a zero.

6

I tried four decimals. didn’t matter.
Also, if I multiplied by 10 instead, the line that is 8% calculated at zero.

BTW - this is 10.2. 400, initially saw this on 10.2.100

7

So this is occurring because the integer is truncating your decimals early (I believe).
You may have to convert number of receipts to a decimal first…

8

Thanks Jason, I made sure everything in process is decimal, still no luck.
If you find something that I missed, that would be great.

My point of this posting is to give a heads up that sometimes you have to think outside of the box to get the results you need.

Here’s the BAQ for anyone to use or review.

LS_zSVSupplierPerfSummed.baq (67.3 KB)

9

Just curious … are variables of type decimal fixed decimal points, or are they like conventional floats and doubles ?

What is the range of values a decimal bar can be?

Floats are ±1.18e−38 to ±3.4e38
And Doubles ±2.23e−308 to ±1.80e308

Edit

Just looked up the SQL decimal, and it looks like it is set during table creation. So what is the precision in E10? Does it depend on the decimals setting during company config?

10

you need to add two brackets at each calculation otherwise program engine will read this as :
A x 100/B x 100 i.e. the second 100 on the numerator so try this ( (Ax100) / (Bx100) )

also on your calcOnTime*100 add [dot] or dot zero to any of the used numbers (100.0) so SQL will activate the decimal calculation method

image
image841×390 20.7 KB

image