9 Replies Latest reply on Mar 27, 2015 11:59 AM by philmodjunk

# Error in Specify Calculation using Let and Average

### Title

Error in Specify Calculation using Let and Average

### Post

I have a schema with some tricky calculated fields and need to calculate a weighted average. For some reason the following calculation:

Let (
TotalArgWeight = Sum ( ArgumentWeight::Amount ) ;
Average ( ArgumentWeight::Amount / TotalArgWeight  )
)

results in this error message: "In the function Average, Count, Extend, GetRepetition, Max, Min, NPV, StDev, Sum, GetSummary or GetNthRecord, an expression was found where a field alone is needed."

It's a simple calc, so I'm sure I'm doing some simple wrong thing, but have been unable to pinpoint the problem. Thanks for your help on this.

• ###### 1. Re: Error in Specify Calculation using Let and Average

It's exactly what the error message says. Average cannot take the average of a math expression such as Amount/TotalArgWeight

It can only take the average of a related set of records or the repetitions of a repeating field.

• ###### 2. Re: Error in Specify Calculation using Let and Average

Thanks Phil, that's exactly what it was. It won't even accept "Average ( ArgumentWeight::Amount / 1 )". So I guess I'm going to have to write some horrendous calculations and use extra fields.

That's okay. I'm coming to FM from Access, and see the move as an improvement.

• ###### 3. Re: Error in Specify Calculation using Let and Average

Take another look at the math.

(a1/b + a2/b + ... + an/b)/n

is what you tried. but doesn't

( (a1 + a2 + ... + an ) /n )/b

give you the same result?

in other words, wouldn't

Average ( ArgumentWeight::Amount ) / TotalArgWeight

produce the same result as what you tried with your expression?

You can also use summary fields to compute averages and they include a weighted average option.

• ###### 4. Re: Error in Specify Calculation using Let and Average

Thanks again Phil !

Silly me, yes it would. Average ( ArgumentWeight::Amount ) / TotalArgWeight is equivalent to Average ( ArgumentWeight::Amount  / TotalArgWeight ).

The reason I made that error is I'm incrementally implementing a complex calculation. The second step is Average ( ArgumentWeight::Amount  / TotalArgWeight * Argument::ConfidenceLevel ).

I've attached a schema image. The Text file is the parent file of the Argument file. What's being calculated in the first step, my original equation, is Text::ConfidenceLevel. Each argument has an ArgumentWeight::Amount and a ConfidenceLevel. I can't just sum their product. I must first normalize the weights and than take the average of the product of each argument.

After I get the first and then the second step going, the additional tables will be used to calculate the ConfidenceLevel for an argument. Currently this is being entered manually for development purposes.

Sure wish I'd picked a simpler first app for FileMaker!  This is for a Political Persuasion Knowledge Base research project. It's a bear of a calculation to implement.

• ###### 5. Re: Error in Specify Calculation using Let and Average

So the relevant table occurrences/relationships for this calc are:

Text----<Argument>-----argumentWeight

and presumably this calculation is designed to evaluate from the context of Text. Now to look at the calculation:

Average ( ArgumentWeight::Amount  / TotalArgWeight * Argument::ConfidenceLevel ).

Seems to me that you can define a calculation field, cAdjustedTerm, (not how you do this in Access!) in Argument defined as:

Amount * ArgumentWeight::confidenceLevel

then you can use: Average ( argument::cAdjustedTerm ) / TotalARgWeight

• ###### 6. Re: Error in Specify Calculation using Let and Average

Thanks Phil, that worked perfectly !

Now I'm faced with a new problem. In the context of the Text table,
Sum (ArgumentWeight::Amount) does not sum the argument weights correctly. It sums only the unique values.

For example with four Argument records in the portal for a Text, .8 + .5 + .8 + .5 = 1.3 when it should equal 2.6.

If there are only the first two records, .8 + .5 = 1.3 which is correct. Changing the first record to .5 results in .5 + .5 = .5 which is incorrect. Adding a third record with a weight of .5 gives .5 + .5 + .5 = .5 which is also incorrect.  This is puzzling.

The relationship between the ArgumentWeight and Argument tables is a one to many with an "=" in the Edit Relationship dialog.

• ###### 7. Re: Error in Specify Calculation using Let and Average

Sum (ArgumentWeight::Amount) does not sum the argument weights correctly. It sums only the unique values.

No it doesn't. It sums ALL related records. Unique values is not a factor in how this works. Better check data and relationships more closely.

I'd guess that "context" may be fooling you into thinking that the two values occur twice when they only occur once. When double checking relationship function, it's often useful to put an unfiltered portal to the same table occurrence on a layout that matches the context in which your calculation evaluates.

And you may find this link on Table Occurrences insightful: Tutorial: What are Table Occurrences?

• ###### 8. Re: Error in Specify Calculation using Let and Average

Thanks. The solution was to add ArgumentWeightAmount to the Argument table. It's a calculated field equal to ArgumentWeight::Amount. Now the form works perfectly and passes all functional tests. On to the rest of the forms!

Looked and studied some of your post on table occurrences. A well written educational treasure. It's info like this that provides a rich foundation on which to build our conceptual castles. Actually I didn't grasp all of it the first time, and so will have to revisit it periodically. It has a prominent position in by folder of Learning FM bookmarks.

Thanks again, Phil. This has been a wonderful first experience with the FM community.