Say you have the following data, which represents the difference, in days, between a ship date and a receive date:

1

2

3

0 --> Not yet received

5

Now, it seems that if I use a Summary Average field, for example, FM would take the sum and divide by 5, which isn't what I want (should be divided by 4 since the 0 means I haven't received the item yet.).

I realize FM won't understand my 0 to mean "don't include", but I don't see any way to tell the Summary Field Function to ignore a value based on some rule (an if statement, for example).

So, my options that I know about would be to create a script where I control how the average computes and do the calculation manually using some trigger?

----

Is there a built-in FM function that would do this calculation where I can configure how the calculation happens like I can with a regular calculated field? Not sure how to handle this situation.

I did check the documentation, but possibly missed a better way to do this.

Thanks in advance,

- m

Using empty instead of 0 is the simplest.

Or use SQL

ExecuteSQL ( "

SELECT AVG(CASE WHEN diff>0 THEN diff END)

FROM table

WHERE condition

";"";"")