5 Replies Latest reply on Sep 19, 2014 11:57 AM by philmodjunk

    Averages

    john9210

      Title

      Averages

      Post

      FM 12 adv. I have records with cost data. I want to get 7 day averages of costs so I can use them on a graph. I can’t figure out how to do this. For example: Say I have 100 records, I want to sort them in order and then get the average of the first 7, the second 7, the third 7, etc.

       

        • 1. Re: Averages
          philmodjunk

          If you can sort your records on a single field to group them into the 7 day record groups that you want, a summary field can be specified for the Y series data that will compute the averages.

          Here's a date calc that returns the same date (The Sunday date) for every record in a given week:

          DateField - DayOfWeek ( DateField ) + 1 //be sure to select Date as the result type.

          Then and "average of" summary field can be specified for the Y-Series.

          Be sure to select the data source options for "summarized data" for your chart.

          • 2. Re: Averages
            john9210

             No I don’t have a single field for grouping the records. I sort them by primary key to get them in the order of creation date. Then I want the average by group. I’m not trying to group by week. When I said 7, that is misleading. It's just a number I picked for discussion purposes. Actually, I would like to group by “n” records where “n” can be any value.

            • 3. Re: Averages
              philmodjunk

              I used week as a simple example of how you have to do this. However the details, to group the records for your Y-series average, you'd need a field where every record in the group has the same value in a field which you then use as the sort field.

              Maybe a different approach is in order. You might keep this data in a related table and set up a match field table as the basis for your chart. You'd use a script to generate records in this table just for charting purposes. These records could use a text type match field that stores a return separated list of primary key values. You Y-Series values would be set up to use the Average () aggregate function.

              • 4. Re: Averages
                john9210

                I have the data in a separate table, but I don't see how to set it up.

                • 5. Re: Averages
                  philmodjunk

                  Let's say that you have the following values in that table:

                  ID    Value
                  1      2.5
                  3      4.8
                  4      5
                  6      2

                  Then a text field in a related table with the values:

                  1¶3¶4

                  Could be used as a match field to your table of values and it would match to the first 3 values. Average could then be used to compute the average of those three values via the relationship.

                  The challenge will be in producing that return separated list of values. such that each record in this new table matches to the correct N records from your table of values.