5 Replies Latest reply on Oct 19, 2010 8:51 AM by etripoli

    Working out the mean of a set number of records

    Jon1009

      Title

      Working out the mean of a set number of records

      Post

      Iam trying to work out the mean of a set number of related records say the last 5 records. Iam just wondering whats the best way to get the data from the last five records to work the mean out?

      Ive have the data displayed in a portal

      Thanks Jon

        • 1. Re: Working out the mean of a set number of records
          philmodjunk

          You have two options you can explore: Average ( RelatedTable::Datafield ) and a summary field defined in the portal table that computes the "Average of" this same field.

          • 2. Re: Working out the mean of a set number of records
            etripoli

            Try using Average ( RightValues ( List ( Related Table::Field ), 5 ) ) to get the average of the last 5 values, and place it in a calculation field in the parent table.

            • 3. Re: Working out the mean of a set number of records
              Jon1009

              Thanks guys managed to figure it average bit out

              now I have the mean of the data in a field iam looking to somehow count them for instants is the data is > the mean 9 consecutive times a e-mail will be sent same if is < the mean 9 times in a row

              what would be the best way to achive this? have two files BelowMean and AdoveMean and in a script something like

              IF DataField > AverageField

              +1 AdoveMean

              SetField BelowMean 0

              Else IF DataField < AverageField

              +1 BelowMean

              SetField AdoveMean 0

              Are would you recommend another way?

              • 4. Re: Working out the mean of a set number of records
                philmodjunk

                I think you need to explain what you are trying to do in more detail. "> the mean 9 consecutive times" is that a newly calculated mean each time or the original mean? If it's recalculated each time, what are the data sets used each time?

                I'm guessing you may need to set up a relationship for this to keep track of the mean for each set of related records, but could easily be wrong here.

                Giving an example of how you want this to work would help fill in some of the blanks.

                • 5. Re: Working out the mean of a set number of records
                  etripoli

                  Seems like you're on the right track.  However, besides checking if the value of the field current record is above or below the mean, you also need to keep track of the previous value, i.e. whether the values are consecutive, and the number of times the value has been consecutive.  Tracking these using variables would be better than creating fields.  And, if the values are not consecutive, then all the variables need to be reset.