Conflicting Averages?

[comment]

Could someone please explain the difference between a summary field average and a calculated one?

Here is a file showing an interesting discrepancy between tho two.

averages.zip

[Søren Dyhr]

They observe the typecast of the "n/a" value differently ...you need to Abs(Choise) to get rid of the third value ...and both behave equal!

--sd

[comment]

I believe that Average = Sum/Count.

Count, according to FMP help, is "the number of valid, non-blank values".

IMHO, a text entry in a number field is NOT a valid value. Therefore, the Summary Average is correct in ignoring it, and the Average function is wrong in counting it.

(However, the Summary Count does count it, as does the Count function. So it appears that in a summary field Average <> Sum/Count. The new attached file shows this very clearly.)

Using Abs() is essentially validating the non-valid value. So of course it appears to "solve" the problem, but in fact it is merely ignoring it. As it happens, I would like to get the CORRECT average across a relationship.

averages2.zip

[Søren Dyhr]

Using Abs() is essentially validating the non-valid value. So of course it appears to "solve" the problem, but in fact it is merely ignoring it.

Yes I had my doubt of which was which, but had to think it through...

--sd

[comment]

Here the plot thickens... (see file no.3)

It seems that ABS(x) achieves the same as = x (result number), but NOT the same as GetAsNumber(x).

I find this somewhat ridiculous.

averages.zip

[Søren Dyhr]

Yes it's obvious that ChoiceAsN which behaves correctly

...but no it isn't ridiculous it's an ever ongoing debate on Empty, zero and void ...the issue when looping choosing between IsEmpty or "" for the scripts progressing. It's simply the machine implementation of math-rules in the inner mechanics. It's our duty to gain knowledge of such discrepancies, in order to pick the best of decent solutions.

Another option that behaves as supposed to is: Filter ( Choice ; "1234567890" )

But Average over a relation is always tricky since it isn't obvious which number of records to count as well as summing. Is it the ones with something entered or all dispite value even void...

So I would think that we have learned is - when Avarageing over a relation must this precaution be made:

Average ( All::ChoiceAsN ) where ChoseAsN = GetAsNumber ( Choice ) or Filter ( Choice ; "1234567890" )

--sd

[comment]

It's our duty to gain knowledge of such discrepancies

I agree. Therefore the following is purely a philosophical observation (or bitching, if you like)

The fact that Summary Average counts differently than Summary Count is ridiculous. In fact I would say that is a bug;

The fact that [numberfield] <> [calculation, number = numberfield] is also ridiculous.

So I would think that we have learned is - when Avarageing over a relation must this precaution be made:

Average ( All::ChoiceAsN ) where ChoseAsN = GetAsNumber ( Choice ) or Filter ( Choice ; "1234567890" )

I would say we have learned more than that:

When averaging, you have a choice to count non-valid values or not; you also have a choice of averaging over a relation or in a Summary field. I believe my last file shows how to handle each of the 4 possible combinations.