week calculation

[blissland]

i have a field which stores the date of the monday of the week that the record's data is entered. to give a more sequentialness to it, i have another field with the following calculation:

Right (Year ( Get (WeekDate) ); 2) &

Right ("0" & WeekOfYear ( Get (Weekdate) ); 2)

so that i get values like 0801, 0802, 0803, etc.

this year, the dates were 12/31/07, 1/7/08, 1/14/08.

Question 1: why do the above dates produce 0753, 0802, 0803, skipping 0801?

Question 2: How can I create a field that gives me a nice sequential 1,2,3,4,.... so i don't have to figure out how to count from xx53 to xx01 (or xx02 !)? I assume that I could create a calculation that would determine 'weeks since some specified date', but is it gonna choke for the same reason that made 0801 not appear?

[Vaughan]

What is the Get( WeekDate ) function?

[blissland]

oh gosh, my bad. i copied it wrong. just take the 'get' out of there and leave the dateweek field.

i've replaced this now with :

Abs((WK__Client::startdatemonday - DateWeek) / 7)

to give me a nice sequence. but i still want to know why 0801 got skipped.

[Vaughan]

"Question 1: why do the above dates produce 0753, 0802, 0803, skipping 0801?"

The first week in 2008 starts on Tuesday. The first Monday of 2008 is in the second week of the year. The result reflects this and is correct.

I don't think it's correct suggesting that the Monday before Tuesday January 1 2008 is in the first week of 2008. It's not, it's in the last week of 2007.

I'd be re-thinking this whole Monday-week thing.

[blissland]

i see. so new year's day is considered to be in the first week of january, not the last week of december. so some weeks don't have 7 days associated with them...its just a matter of rows. so if new years was on saturday, the first week of 08 would have one (2?) day(s) associated with it.

[blissland]

ok, so moving onto the script that is depending on these things:

I'm wanting to determine how many weeks of data to loop thru, so i'm looking at the project start date (which is entered manually) and the current date and determining how many weeks are in between. the following calc does that nicely. however, it only works if i put in that october date. if i put in the DATE field (same table and table occurrence as the calculation (date) field after the minus sign), script debugger reveals that the field data is never registered, leading to "[] minus currentdate" . Why would it work for one date field but not the other.

Round(

Abs(

Date(10;1;2007) - WK__Client::Mon_current_c

) / 7

;0)

[Vaughan]

I edited my post to clarify some points while you typed your reply...

AFAIK a week starts on Sunday for most countries (but it's Monday for some localisations of FMP so don't assume anything) so the first week of a year could be less than 7 days -- and is probably likely to be.

I've found that any unexpected behaviour -- like the missing of 0801 -- is usually worth exploring deeply to find the explanation, rather than finding some work-around that produces the expected result. Usually I discover I've made an incorrect assumption about something.

[Vaughan]

ok, so moving onto the script that is depending on these things:

I'm wanting to determine how many weeks of data to loop thru, so i'm looking at the project start date (which is entered manually) and the current date and determining how many weeks are in between. the following calc does that nicely. however, it only works if i put in that october date. if i put in the DATE field (same table and table occurrence as the calculation (date) field after the minus sign), script debugger reveals that the field data is never registered, leading to "[] minus currentdate" . Why would it work for one date field but not the other.

Round(

Abs(

Date(10;1;2007) - WK__Client::Mon_current_c

) / 7

;0)

Well, this is a different issue all together.

It might be time to post your file to the Forums because there are so many things that could be causing the problem.

[blissland]

ok. fair enough. here's my file. its the last script listed. comments in the script detail the issues including:

1. how do i make the script continue automatically when there's no found set?

2. what's the correct Tab character?

3. why won't it handle my 'projectstart' date field. i've disabled this variable. i've also set the variable 'weeklast' to a fixed variable in order to be able to run thru other parts of the script.

FIP.fp7_5.zip

[blissland]

while i'm at it....is there a secret to getting excel 2008 for mac to import filemaker pro 9 data? i go thru all the steps--it sees all my layouts and tables and fields--and then when i go to import it, it says 'error...object not found'.

[blissland]

Well, I attached the file but managed to resolve all the issues myself.

So, a new question....is there a way to attach more than one file to an email. Seems like its only possible to attach one file.

[comment]

To get a sequential number of a date's week, with weeks starting on Monday, you can use:

Div ( Date - 1 ; 7 )

This starts with 0 being the number of the first week in Filemaker's date range (i.e. the week of 1/1/0001), so the current week would be 104757.

To start at some other week, just subtract a constant from the result, e.g.

Div ( Date - 1 ; 7 ) - 104706

will return 1 for the week of Oct 10, 2007.