2 Replies Latest reply on Mar 22, 2014 4:40 PM by SeanLang

    Average based on one group of values

    SeanLang

      Title

      Average based on one group of values

      Post

           Hi,

           I have my basic three layer DB:

           Projects

                 Buildings

                       Issues

           I have the user enter the progress of the buildings construction (0 - 100%)

           I need to be able to get an average of all buildings progress within ONE project.

           so, if there are four buildings within one project, i need an average of the four buildings only. I am not sure how to write the average function which is Average ( field { ; field }). For some reason, when I try to use this with just the field name in the brackets, I only get the first buildings number, not the entire set.

           Why does it have (field { ; field})? why field twice? am I supposed to enter something for each "field"?

        • 1. Re: Average based on one group of values
          philmodjunk

               There are three syntax options to an aggregate function:

               Average ( Field1 ; Field2 ; field3 ) will average the values of these three fields that are all defined in the same record. That's the forum you are seeing described as: Average ( field { ; field })

               If you just use Average ( Field ), that's the same as asking "what is the average of 5? and the average of 5 is 5.

               But you also can use:

               Average ( RepeatingField ) where averages computes the average (mean) of all the values stored in repetitions of the repeating field

               And the option you should use is:

               Average ( Buildings::CompletionPct ) where you compute the average from all related records. The key is to define this as a calculation field in Projects, not Buildings.

               Note: An "average of" summary field can also be used to compute the same average.

          • 2. Re: Average based on one group of values
            SeanLang

                 Thanks, Once I figured out what table to base it on, it seemed to work perfectly.

                 ...Getting there! This program sure is fun to learn! It hardly seems like work.