6 Replies Latest reply on Dec 3, 2011 8:12 AM by DavidMaloney

    creating a field in a record with limited summary data

    DavidMaloney

      Title

      creating a field in a record with limited summary data

      Post

      I can't figure this out.  I have a database with a series of records, dated daily over the past year containing a lot of fields with numerical data from each day.  I would like to include a field in each record that contains summary information from a single field- but only from a limited set of records (not the entire record set).  ie the current week, or past week, or past month etc.  I do not see how to do this.  I can find the day of the week and thus the start and end date that I am interested in, but I can not figure out how to limit the summary field (for example the average of, or total of) to those specific dates.  

      I would like the data to be updated as I add new daily records.  Is there any way to do this?  It would seem simple, but I have not been able to figure it out.

      Thanks for considering.

        • 1. Re: creating a field in a record with limited summary data
          philmodjunk

          You can use a summary field to compute aggregate values such as the total, average, standard deviation, count...

          To get this for a specified set of records, you can perform a find or you can access the summary field from a related table where the relationship serves to link only to the sub set of records in question and then your summary field reports the aggregate of that set of related records.

          • 2. Re: creating a field in a record with limited summary data
            DavidMaloney

            I do not see how a related table could link to a "moving" set of records.  For example, I would just like to have a field that is the average of this field over the last 7 records (days), but that last 7 day average should then be different on every record that I look at. 

            • 3. Re: creating a field in a record with limited summary data
              philmodjunk

              I said it could be used, not that it would be simple or a good idea in your case. A simple summary report is much easier to set up and very flexible to use. It can be done as you can set up relationship that match to a range of values instead of a single value by using inequalities. I suggested a second alternative that's much simpler for most reports of this type.

              Using the summary report, you find "all records over the last 7 days" and then would sort those records accordingly to get the needed averages or other aggregate values.

              but that last 7 day average should then be different on every record that I look at.

              You'll need to explain in more detail how your data is organized before I can go into details or possibly suggest a different approach.

              • 4. Re: creating a field in a record with limited summary data
                DavidMaloney

                Let me clarify my original question.  For a simple example, say I have a database that has a date field (date) and a number field (miles_run) and I have 365 records for the past year with this data, and about 75% of the days have the miles I ran that day and the other 25% just have a date entry and a blank entry on the days that I did not run.

                I know I can do a find request for a given date range and a summary field (summary_miles_run) would give me the total or average or min or max etc of that found set of records, but that is not what I want to do.

                I would like to make a field in each record that gives the total number of miles ran in the preceding 7 days- and that field would be in every record (and would have a differnt result on every day depending on how many times and how far I ran the prior 7 days).

                That is all that I am trying to do (except use other intervals rather than just the prior 7 days, ie month etc).

                Thanks again

                • 5. Re: creating a field in a record with limited summary data
                  philmodjunk

                  Is the interval always the same or something the user would select?

                  A self join relationship can match to all records in the same date to 7 days ago interval and then either a sum function or a summary field (from the self join's table occurrence) will compute the 7 day total.

                  If your records are sorted so that the records in question are in a consistent order, you may also be able to use a calculation field with the getNthRecord function. This isn't always possible, but it is a method that does not require adding a relationship to your system.

                  Here's a relationship, that matches all records with the same date upto a user selected number of days in the past:

                  MileageTable::Date > PreviousRuns::Date AND
                  MileageTable::cDate2 < PreviousRuns::Date

                  Define a global field, gDaysPast and then define cDate2 as a calculation field that returns a date:

                  Date - gDaysPast (if you want the interval to always be 7 days, just use Date - 7 and don't use the global field.)

                  PreviousRuns is an additional table occurrence of MileageTable to make this a self join.

                  You can then create a report layout based on MileageTable and put summary fields from PreviousRun on your layout. By specifying different values in gDaysPast, you get matches to different numbers of related records and the summary fiels report aggregate values (totals, averages, etc) of the resulting set of related records.

                  • 6. Re: creating a field in a record with limited summary data
                    DavidMaloney

                    Thanks very much for your help.  We will see if I can get this to work.

                    The records are always in sorted order by date (and also by record number).

                    I do not need the interval to be user selected.  I would like to program that into the field.