Week of the Year

[selzlerb]

Hello Team,

I have a database with a table for "absences". Each absence record has a field for the absence date, and a field that displays the week of the year that the absence is in. So a given record would have the following fields;

Absence Date: 10/13/2006

Week Of Year: 41

Of course there are hundreds of absence records, and they will be from various dates and weeks of the year.

I need a calculation that will identify the week of the year that has the most absences for the found set of records. I'm hoping for something along the lines of "the week of the 6th of November" or "the week of 11/06/2006 to 11/11/06" Something along those lines.

I would be nice to be able to apply the calculation to the "months with the most absences" as well as the "day(s) with the most absences" as well.

Any ideas along these lines would be greatly appreciated.

Thanks.

- Sez

[Søren Dyhr]

Of course there are hundreds of absence records, and they will be from various dates and weeks of the year.

This means that there is no excuse from a real subsummary report, where you along the path if you're using the wizzard to make it stumbles over a dialog asking for sorting ...here are we with fm8.5 blessed with the ability to use the reorder by summary field ...looking almost like the image above.

I have made the summary field "count of who" ...

--sd

[selzlerb]

Hi,

Thanks for the reply. I see that you are using a sort to find a frequency, but how do you define the "range of days" as far as the week goes, and how is this reported in this scenario? What data is "Who" collecting? And what function does "get the week" perform? Sorry if I'm being a bit thick.

- Sea

Edited November 11, 2006 by Guest

[LaRetta]

You had also better include the year in that leading part. Otherwise week 41 from 2004 will group with week 41 from 2005 etc.

[Søren Dyhr]

Indeed!!! :oops:

--sd

[selzlerb]

Hello Team,

Thanks for all of the replies. However, how do I change the week identified from a number to a meaningful range?

- Sez

[selzlerb]

Hello Team,

I've been looking at the drop down calendar field format, and I'm wondering if there is a way to leverage that to display the "week of the year". Essentially I'd like to be able to translate a number, like week 41, to somthing a little easier to understand and the calendar looks like a good solution. However I can't seem to display the found week of the year in the calendar itself.

Anyone have any thoughts on this? Or am I barking up the wrong tree entirely?

Let me know what you think.

- Sez

[comment]

The drop-down calendar is an input-only device. It wouldn't be TOO difficult to display a date in a mini-calendar (see here), but I wonder how usable such chunk would be. And what do you do when a week straddles across two months?

Another question is where does your week number come from, and how it handles year boundaries. For example, Filemaker's WeekOfYear() function often splits the last week of the year into two - so if your intention is to group records by week, that is not a good way to go.

[selzlerb]

Hmmm, I didn't know that. I was using the week of the year function to genterate that number. I would actually be happy if I could identify the range of the week using dates. For example;

"the week of 11/06/2006 to 11/11/06" or "the week of the 6th of November"

I'm assuming this can be derived from the "week of the year" or the "day of the year" function, but I have no idea how to find the range of dates.

Thanks.

- Sez

[comment]

OK then, assuming your Week Of Year field is a calculation field, rename it to cWeekStart and change the calculation to:

AbsenceDate - DayOfWeek ( AbsenceDate ) + 2

and set the result type to Date. This calculates the Monday of the week, so you can use that as your break field for sub-summary by week. Being a date, it also sorts properly across years.

For your label, define a second calculation field cWeekEnd (result is Date) =

cWeekStart + 5

In the sub-summary part, type "The week of <> to <>".

[selzlerb]

Wow. This works great! Thank you.

I changed the "weekstart +5" to "weekstart +4" to keep the result within the range of a school week.

Also, I follow the idea behind this, however I don't understand why

"AbsenceDate - DayOfWeek ( AbsenceDate ) + 2"

Gives me the date for Mondays. Why does this calculation always find the Monday?

Thanks again. I really appreciate it.

- Sez

[comment]

Because when you subtract the day of the week of any date from the date, you always get the Saturday of the preceding week.

Or, if you want to look at it from the opposite direction, Saturday is day 0 of the following week, Sunday is day 1, Monday is day 2, etc... Subtract what you just added, and you get back to zero.

[selzlerb]

Interesting. Great explanation. This kind of logic often escapes me. I appreciate the help and the education.

- Sez