FileMaker Reports By Month and Week

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

I was recently asked to provide a report that broke values down by the month and then by the week. This was a billable timecard report and it would show the total timecard hours, the total of hours flagged as billable and the total billable amount.

The layout parts used in this report include a header, a subsummary sorted by the month of which the timecard entry occurred, a subsummary sorted by the week if the year in which the timecard entry occurred and a title footer.

Now this breakdown is a little off and that is because each week doesn’t fit snugly into a given month. So you can see that Week 36 of year 2008 is included in the September 2008 and the August 2008 totals.

In the case of my clients needs, they thought this was fine. They were mostly looking for how the month progressed by each week. The real value they were interested in was the monthly totals. I struggled with a compromise in this area but couldn’t get past the fact that a week almost never starts on the first of the month or ends on the last day of the month. However, my odds did go up a little bit if no timecards are entered on weekends!

The month and week values were captured using calculation fields using the Month and Week Of Year functions.

The Month function will return the number of the month in a date field or text string formatted in a recognizable date value. This will be a whole number between 1 and 12 because there are only 12 whole months available in a year.

The WeekOfYear function extracts the week of the year from a properly formatted date string in a date field. For example the information of 1/24/98 would return 4 only.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.