5 Replies Latest reply on Aug 1, 2009 2:18 PM by comment_1

    How to create a weekly report

    tessgold

      Title

      How to create a weekly report

      Post

      I'm new to FM and am having the hardest time figuring out how to create what I thought would be a simple report. I hope someone can clue me in.

       

      I need to keep track of daily attendance at our organization's playgroups. No problem. I have a table for Client List and another for Playgroup Attendance. We enter each client's dates of attendance and I had no trouble creating a report that lists the total attendance on any particular date. I even added a DayName field so I could easily see what day of the week is associated with each date.

       

      My problem is that it would be nice if I could structure the report so that it would automatically add up a week's attendance for me (Monday thru Friday). I've got over 2 years of data and just need to find an easy way to organize it.

       

      I'm sure I must be overlooking something very basic, because I would think that a lot of people need to do this, but I can't figure it out and I can't find any help in any of the documentation.

       

      I'd appreciate any help anyone can give me.

       

      thanks, Tess

        • 1. Re: How to create a weekly report
          comment_1
            

          You need to add a calculation field that returns a common value for records of the same week, for example =

           

          AttendanceDate - DayofWeek ( AttendanceDate )

           

          and summarize your report by this field.

           

           

           

          Note:

          1.  You don't really need a DayName field - you can format the attendance date itself to show the day name;

           

          2. Similarly,  if you set the above calculation's result to type Date and add + 2 to the formula, it will return the Monday of the week - you can then use it also as a label for the summarized period.

          • 2. Re: How to create a weekly report
            tessgold
              

            Dear Super,

             

            Thanks for the help. This was exactly what I needed. I'm still a little unclear on exactly how the calculation works, but I have a copy of "The Missing Manual" and I'm going to start reading up on date calculations. In the meantime, thanks to you, I've got my report exactly the way I need it.

             

            I'm off to see if I can figure out how to get monthly figures by myself. I really appreciate your super-fast help.

             

            Tess

            • 3. Re: How to create a weekly report
              philmodjunk
                

              You can usually understand how date calculations work if you think of them as number fields that count the number of days from an early arbitrary date.

               

              Datefield + 5, for example returns a date 5 days later than the date stored in "datefield".

              • 4. Re: How to create a weekly report
                tessgold
                   Thanks PhilModJunk. I understand your example because it's pretty straightforward. But I'm obviously looking at this the wrong way, because in the example given above I read the calculation as "AttendanceDate minus the day of the week of the AttendanceDate". That would be the same as saying "August 1, 2009 minus Saturday". Again, obviously, that's not what it means, because it does exactly what it's supposed to by letting me produce a weekly report.
                • 5. Re: How to create a weekly report
                  comment_1
                    

                  tessgold wrote:
                  That would be the same as saying "August 1, 2009 minus Saturday".

                  Almost, but not quite. It's actually "August 1, 2009 minus 7", because DayOfWeek() returns a number, and the number for Saturday is 7.

                   

                   

                  IOW,

                   

                  date - DayofWeek ( date )

                   

                  returns the 0th day of the week in which date falls.