Cornfusing calculation

[David Jondreau]

This one's a doozy for me. I've got a table of Days, a simple Calendar where each record is a unique date. What I'm trying to do is create a calculation that returns a number based on a date's week relative to the current week.

A 'week' is 7 days, Monday-Sunday ideally, but Sunday-Saturday will work. The calc field for a day in this week should return zero. A day last week should return -1, the week before, -2. A day in next week should return 1, and so on.

However, next week, a day in this week should return -1.

Make sense?

I started off with a simple calc where _kp_Date is the date field and gCurrentDate is set by script on start up to the current date.

Let([

thisWeek = WeekOfYear(_kp_Date);

currentWeek= WeekOfYear(gCurrentDate)

];

thisWeek - currentWeek

)

This works fine until I get to early January or late December, then it breaks down once because a partial week at the beginning and end of a year count as a week for WeekOfYear and I'm not sure how to "go around the bend" to work with other years.

Any ideas?

[comment]

Try:

Let ( [

this = Div ( YourDate - 1 ; 7 ) ;

curr = Div ( Get (CurrentDate) - 1 ; 7 )

] ;

this - curr

)

I don't see the advantage of dedicating a field to hold the current date.

[David Jondreau]

Let me see if I understand this...

The -1 is to change the way FM defines a 'week' (to Mon-Sun not Sun-Sat)?

Instead of fooling around with WeekOfYear, you're just using the absolute expression of a date as a number making 'curr' some large number (52*2000)+ that represents "WeekOfC.E."?

Very nice. Thanks so much,

DJ

[comment]

Yes, to both.