1 Reply Latest reply on Oct 10, 2008 8:24 AM by TSGal

    Relating Records to A Common Variable Then Totaling those common records

    Lex262

      Title

      Relating Records to A Common Variable Then Totaling those common records

      Post

      I Have daily reports being created. Each report has various totals - we'll call one total MILES for my example.

       

      I have people inputing into a daily record many variables every day. Each record now contains a field using GetAsNumber (date) to create the Week number that record is a part of.

       

      So for Week 9 in my example (and all future weeks). How would I go about creating a formula that would relate to Miles and sum the miles fields for each week separately?

       

       

        • 1. Re: Relating Records to A Common Variable Then Totaling those common records
          TSGal

          Lex262:

           

          Thank you for your post.

           

          When you use GetAsNumber (date), it will return the number of days since 1/1/0001.  To get the week number, you may want to consider WeekOfYear (date).  This returns a number between 1 and 52.  The caveat is that it doesn't reference the year, so you could have the same value for records in different years.  Another possibility is to use the DayOfWeek (date) which returns the value 1 through 7 for Sunday through Saturday, and subtract this number from the date to get you back to the beginning of the week.  For example, using 10/10/2008 as an example,

           

          DateField - DayOfWeek (DateField) + 1

           

          10/10/2008 - 6 + 1

          = 10/5/2008 (which equals Sunday; the beginning of the week).

           

          Then, you can use this date field to summarize the data.  That is, sort by the calculated date and have a sub-summary report by this calculated date.  Does that make sense?

           

          If you need further clarification, please let me know and I'll delve into it a bit more.

           

          TSGal

          FileMaker, Inc.