6 Replies Latest reply on Oct 15, 2008 1:29 PM by cutnpaste

    Calculating Across Date Range by Days of Week



      Calculating Across Date Range by Days of Week


      I want to calculate productivity or total units by Week. For example Monday through Sunday every week Total Hours.


      Workers working 8 hours per day in this range would have 56 hours by Sunday.


      Is there a date calculation that can work for this as records are created? There may not necessarily be a record created for every day, so preferably, each new record would have a these fields totaled. Not sure which direction to go on this:


      Each record to have:


      Total Time Cumulated For Infiniti dating to beginning of records 

      Total Time Cumulated for Year

      Total Time Cumulative Monday Through Sunday, each day increasing this field as it looks back to Monday and gives a running total. Starts over again once new Monday starts up.


      Seems like this wouldn't be too hard since people have got to be tracking payroll hours somewhere...


        • 1. Re: Calculating Across Date Range by Days of Week



          Thank you for your post.


          There are several ways to approach this.


          I'm not 100% sure what you mean by a date calculation as records are created.  That is, do you want a new date to appear each time you add a record?  Is that the next day?  Then, you mention that there may not be a record created every day.  Is there some consistency at all?  One option to consider is there are field defaults when adding records.  You could have a date field default to entering today's date.  That way, when you add a record, today's date is automatically entered.


          A Summary field allows you to gather information from one or more records.  In your instance, you want to add/total records.  Although you can display the same Summary field in a report sub-summarized by week, year and grand total, you will need three separate Summary fields to display in Browse mode.


          Let's look at each of your three requirements.


          1. "Total Time Cumulated For Infiniti dating to beginning of records"

          This is straight-forward.  Create a Summary field, "Total Hours", and make sure it is set to a Total of "Hours".  For every record, this will show you the total for the entire found set of records.


          2. "Total Time Cumulated for Year"

          This is a bit more complicated.  We have to make sure this totals records only for the current year.  Therefore, we need to create a Calculation field to determine the current year, and a summary field to total this Calculation field.  Therefore, create a Calculation field "YearHours" with the following formula:

          If (Year (DateField) = Year (Get (CurrentDate)), Hours, 0)


          That is, if the year of the field "DateField" equals the year of today's date, then we capture the Hours.  Otherwise, we enter 0 (zero).


          Next, create a Summary field "Total Hours This Year" that totals the "YearHours" field.


          The caveat with this approach is that when the year changes, you lose all data from the previous year.  If you wanted to keep the results from the previous year, then instead of having "YearHours" calculation field, you would need one for each year.  For example, "2008Hours", and the formula would be:

          If (Year (DateField) = 2008, Hours, 0)


          Then, the Summary field would be "Total 2008 Hours".  You would do the same for 2007, 2006, etc. 


          3. "Total Time Cumulative Monday Through Sunday, each day increasing..."

          Since you need a total each week, I don't expect you to create 52 separate calculation fields and 52 summary fields.  Therefore, this is probably best done with a script.


          Create a new Number field, "Running Total".  This will display the running total that will be populated by the script.


          Pull down the Scripts menu and create a new script.  You can name this "Create Running Totals".  Here is the script:


          Sort Records [Restore - sort by DateField ascending]

          Go to Record/Request/Page [First]

          Set Variable [$weekbegin; Value: 1/1/1900]

          Set Variable [$running; Value: 0]


             If [ ((DateField - 1) - DayOfWeek (DateField - 1)) <> $weekbegin]

                Set Variable [$weekbegin; Value: ((DateField - 1) - DayOfWeek (DateField - 1))

                Set Variable [$running; Value: 0]

             End If

             Set Variable [$running; Value: $running + Hours]

             Set Field [Running Total; $running]

             Go to Record/Request/Page [Next; Exit after last]

          End Loop



          This script begins by putting/sorting the records by date order and moving to the first record.  This ensures that we start at the beginning.


          We will need two variable to keep track of the running total, and for the beginning of each week.  The running total should be set to zero, and I entered a early date that will not match any existing dates.


          Now, we enter a loop so we can evaluate each record.


          First, we check to see if this record falls into the current week.  Since the DayOfWeek function returns a value 1-7 for Sunday through Saturday, and your work week is Monday through Sunday, we need an offset of one day.  If the DateField starts a new week, then we reset the beginning of week variable and set the running total variable to 0 (zero).


          Next, we add the hours and put that information into the Running Total field.


          Finally, we skip to the next record and go back to the top of the loop.  If this is the last record, we leave the Loop and the script ends.  You will then have running totals for each day.


          Please let me know if you need clarification for any of the above steps.



          FileMaker, Inc. 


          The If() clause looks a bit complicated.  The DayOfWeek function returns the values 1 through 7 for Sunday through Saturday 




          • 2. Re: Calculating Across Date Range by Days of Week

            Wow. Thank you. Some of this occurred to me since the posting, but I didn't know yet how to empliment it. I think you've got me covered in all areas on things I need to sum by week and year and overall. Yeah, I thought of the year calculation - didn't at all consider I'd lose the values when 2009 hit though. Ugh Y2k all over again - right?


            OK, one last imposition. I can probably find it now with your direction you've already given, but since Months have different numbers of days, and I also would like to total by Month (yeah I didn't mention - sorry) should I use a variation of the date application you use, but using If the name of the month contains Jan, Feb, etc and format my dates to display that way? I haven't done any formulation based on characters in place of numbers - just wondering if that would work.


            • 3. Re: Calculating Across Date Range by Days of Week



              I'm glad I was able to help.


              I don't think I would worry about the number of days in a month.  You can always use the Month() function to extract the month.  However, just the Month() function alone would put the amounts from January 2008 and January 2009 together.  You may want to have a "YearMonth" calculation field, where the calculation would be:


              Year (DateField) & Right ("0" & Month (DateField), 2)


              This puts the year together with the month field.  Notice the Right() function.  This ensures the month is two digits in length for sorting purposes.  Otherwise, for 2008, you would have 20081, 20082, 20083, 20084,... 200812.  When you then sort by "YearMonth", the sort order would be: 20081, 200810, 200811, 200812, 20082, ... 20089 which is incorrect.  By forcing a "0" in front ensuring 2 digits for the month, the sort order would be 200801, 200802, 200803,... 200812.  Does that make sense?


              You can then use the above post to either have a sub-summary report, or use the same script modified to use the "YearMonth" field.


              Let me know if you run into any difficulty.



              FileMaker, Inc. 

              • 4. Re: Calculating Across Date Range by Days of Week

                I had a moment of foggieness when I started this, I now have a had a moment of clarity. I'm going to tell you what I ended up going with and am just getting ready to implement the script you suggested for weekly totals. It occurred to me that I left out the detail that in my weekly "view" I'm using Portals. I have relationships pointing back to my main database and created ones for each day of the week. 


                The week view is a quick summary of what happened in each days records.


                Now - is there in this manner of viewing the records I don't have any other method I should consider instead of a script (or the 52 calculations as you mentioned of course). I'd like it to be automatic and not have to hit a button to refresh the script - unless there's a way to make the script always refresh when the layout displays.


                • 5. Re: Calculating Across Date Range by Days of Week



                  Thanks for the additional information.


                  If you want to show up-to-date information in Browse mode for each week, then you will need the 52 calculations and 52 summary fields.  Otherwise, the only two options are refreshing the script or display the weekly information in a Sub-Summary report/preview.  You could always click a button that switches to Preview mode, and when click continue, you are returned to Browse mode.


                  If anyone else has a suggestion, I'd like to hear it.



                  FileMaker, Inc. 

                  • 6. Re: Calculating Across Date Range by Days of Week

                    It seems to me all you need is a couple of relationships.


                    let's call the table where you hours are history and the table where you want your sums query.


                    Create a table occurrence of history and call it infinity.

                    Create a relationship where the infinity::date < query::date (the query date should be a global date field, so you can check other years/dates out)

                    This lets you calculate the sum(infinity::hours) on your query table. You get the total of all hours before query::dateall the way back to infinity.


                    Create a table occurrence of history and call it thisYear.

                    Create a relationship like this : year(thisYear::date) = year(query::date).

                    The sum(thisYear::hours) gives you the total for the queried year. If you want the total for dates prior to query::date but still on the same year, add a dayofyear(thisYear::date)< dayofyear(query::date) to the relationship


                    The third one is trickier.

                    Create a table occurence of history and call it thisWeek.

                    The troubles are these :

                    -You might not start your fiscal year on jan 1. (startingDay, below)

                    -your first week might not start on a monday

                    Therefore, you probably end up with more than 52 weeks in a given year.


                    Set up these number fields : 

                    year(thisWeek::date)&"."&right("0"&WeekOfYearFiscal ( thisWeek::date ; startingDay );2)

                    year(query::date)&"."&right("0"&WeekOfYearFiscal ( query::date ; startingDay );2)

                    2008.47 or 2007.02


                    When you set up a relationship using these, the sum you get though the relationship should be the total for the current week. If you want the total, up to a given date, for that week, add the dayofyear(thisYear::date)< dayofyear(query::date)


                    This is the way I read you request. If I haven't understood properly, please ignore this.