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

    Error in Specify Calculation using Let and Average

    JackHarich

      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
          philmodjunk

          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
            JackHarich

            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
              philmodjunk

              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
                JackHarich

                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
                  philmodjunk

                  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
                    JackHarich

                    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
                      philmodjunk

                      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
                        JackHarich

                        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.

                        • 9. Re: Error in Specify Calculation using Let and Average
                          philmodjunk

                          You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

                          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
                          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

                          Caulkins Consulting, Home of Adventures In FileMaking