Magic Nodes 4.2 w/ 380 masks & real GUI (2011-07-01)[MM2+]

[Bex]

Impressive! I will do some testing and let you know how it works.

Many thanks!

[Bex]

ZvezdanD,
I get some strange results with your formula:

[ZvezdanD]

ZvezdanD,
I get some strange results with your formula:

Yeah, I see. There is a problem when a resulting year is greater then source year. Sorry pal, I already spent too much time calculating this formula. If you want, try for yourself to improve it. Except of approx. two last weeks in year, all results should be correct, Also, you should know that strftime('%w', Date) has totaly different logic for calculating week of day than Jet Format. With Format, every fist January has week of year = 1, no matter which is firstdayofweek. But, with strftime('%w', Date) you could get 0 or 1 depending of year. Here is a code and its result:

Code: Select all

Dim i As Integer, dt As Date
dt = DateSerial(2003, 12, 21)
Debug.Print "Date - FDOW: Sun - FDOW: Mon"
For i = 1 To 21
    Debug.Print dt & " - " & Format(dt, "ww", 1) & " - " & Format(dt, "ww", 2)
    dt = DateAdd("d", 1, dt)
Next


Date - FDOW: Sun - FDOW: Mon
21.12.2003 - 52 - 51
22.12.2003 - 52 - 52
23.12.2003 - 52 - 52
24.12.2003 - 52 - 52
25.12.2003 - 52 - 52
26.12.2003 - 52 - 52
27.12.2003 - 52 - 52
28.12.2003 - 53 - 52
29.12.2003 - 53 - 53
30.12.2003 - 53 - 53
31.12.2003 - 53 - 53
1.1.2004 - 1 - 1
2.1.2004 - 1 - 1
3.1.2004 - 1 - 1
4.1.2004 - 2 - 1
5.1.2004 - 2 - 2
6.1.2004 - 2 - 2
7.1.2004 - 2 - 2
8.1.2004 - 2 - 2
9.1.2004 - 2 - 2
10.1.2004 - 2 - 2

Here is what I get with SQLite and my formula:
Date - FDOW: Sun - FDOW: Mon
21.12.2003 - 52 - 51
22.12.2003 - 52 - 52
23.12.2003 - 52 - 52
24.12.2003 - 0 - 52
25.12.2003 - 0 - 0
26.12.2003 - 0 - 0
27.12.2003 - 0 - 0
28.12.2003 - 1 - 0
29.12.2003 - 1 - 1
30.12.2003 - 1 - 1
31.12.2003 - 1 - 1
1.1.2004 - 1 - 1
2.1.2004 - 1 - 1
3.1.2004 - 1 - 1
4.1.2004 - 2 - 1
5.1.2004 - 2 - 2
6.1.2004 - 2 - 2
7.1.2004 - 2 - 2
8.1.2004 - 2 - 2
9.1.2004 - 2 - 2
10.1.2004 - 2 - 2

Anyway, could you tell me why you need this?

[Bex]

It's used in my Play History & Stats script which is configurable to cater for any FirstDayOfWeek. But I think I'll go with ISO standard and make all the weeks starts on Mondays. It might disappoint some US users and others who consider Sunday (or any other day) to be the first day of week. In fact I have already coded the most part of the script in that direction. But I will of course change it If the dev's implement the needed functions, until then I'll go with Mondays.

Big thanks for your efforts. They are really appreciated!

[ZvezdanD]

But I think I'll go with ISO standard and make all the weeks starts on Mondays.

Maybe, I was not clear. You need to use some complicated formula, even if you decide to limit firstdayofweek to Monday. If you want a script with same behavior within MM2 and MM3 it is not enough to replace Format(Date, "ww", 2) with strftime('%w', Date), because its internal computation is totaly different. Please, take a look on this result:

Code: Select all

Date    -  Jet - SQLite     Date    -   Jet - SQLite
2000-01-01 - 1 - 0          2000-12-01 - 49 - 48
2001-01-01 - 1 - 1          2001-12-01 - 48 - 47
2002-01-01 - 1 - 0          2002-12-01 - 48 - 48
2003-01-01 - 1 - 0          2003-12-01 - 49 - 48
2004-01-01 - 1 - 0          2004-12-01 - 49 - 48
2005-01-01 - 1 - 0          2005-12-01 - 49 - 48
2006-01-01 - 1 - 0          2006-12-01 - 49 - 48
2007-01-01 - 1 - 1          2007-12-01 - 48 - 48
2008-01-01 - 1 - 0          2008-12-01 - 49 - 48
2009-01-01 - 1 - 0          2009-12-01 - 49 - 48
2010-01-01 - 1 - 0          2010-12-01 - 49 - 48
2011-01-01 - 1 - 0          2011-12-01 - 49 - 48
2012-01-01 - 1 - 0          2012-12-01 - 49 - 48
2013-01-01 - 1 - 0          2013-12-01 - 48 - 47
2014-01-01 - 1 - 0          2014-12-01 - 49 - 48
2015-01-01 - 1 - 0          2015-12-01 - 49 - 48
2016-01-01 - 1 - 0          2016-12-01 - 49 - 48
2017-01-01 - 1 - 0          2017-12-01 - 49 - 48
2018-01-01 - 1 - 1          2018-12-01 - 48 - 48
2019-01-01 - 1 - 0          2019-12-01 - 48 - 47
2020-01-01 - 1 - 0          2020-12-01 - 49 - 48


I'll send you on PM a table/algorithm of date mappings depending of the first day in a year.

[Bex]

Yeah, you're right. This is a bit more complicated then I thought. I'll see what solution I come up with.

Thanks again for your efforts!

[ZvezdanD]

Here is a replacement for Format(Date, "ww", firstdayofweek) in SQLite which works correctly for all dates:

Code: Select all

cast(strftime('%W', Date) as integer) +
case when firstdayofweek = 1
then
    case when cast(strftime('%w', Date, 'start of year') as integer) = 1
    then
        0
    else
        1
    end
else
    case when cast(strftime('%w', Date, 'start of year') as integer) > 1
        and (firstdayofweek > cast(strftime('%w', Date, 'start of year') as integer)
        or firstdayofweek = 0)
    then
        case when cast(strftime('%w', Date) as integer) = 0
            or cast(strftime('%w', Date) as integer) >= firstdayofweek
            and firstdayofweek > cast(strftime('%w', Date, 'start of year') as integer)
        then
            2
        else
            1
        end
    else
        case when cast(strftime('%w', Date) as integer) = 0
            or cast(strftime('%w', Date) as integer) >= firstdayofweek
            and firstdayofweek > 0
        then
            1
        else
            0
        end
    end
end

[Bex]

Thanks a lot!

[Teknojnky]

But I think I'll go with ISO standard and make all the weeks starts on Mondays. It might disappoint some US users and others who consider Sunday (or any other day) to be the first day of week.

hrmm, 98273498372 billion americans can't be wrong!

[Bex]

I have actually written a piece of code which gives the ISO year & week. It seems to work just fine even if it's messy. It's converted into a VBscript function that writes the sql to be used: (I've made a shortcut so the script only works from 1998 to 2019.)

Code: Select all

Function IsoYearWeekSQL(date)
IsoYearWeekSQL = _
"case "&_
" when strftime('%w', "&date&", 'start of year') in ('1','2','3','4') then "&_
"      case when strftime('%w', "&date&", '+1 year', 'start of year','-1 day') in ('1','2','3') "&_
"            and (((cast(strftime('%j', "&date&") as intiger) + cast(strftime('%w', "&date&", 'start of year') as intiger) - 1-1)/7)+1) = 53 "&_
"      then strftime('%Y w ', "&date&",'+1 year')||'01' "&_
"      else strftime('%Y w ', "&date&")||substr('0'||(((cast(strftime('%j', "&date&") as intiger) + cast(strftime('%w',"&date&",'start of year') as intiger)-1-1)/7)+1),-2,2) end "&_
" when strftime('%w', "&date&", 'start of year') in ('5','6') then "&_
"     case  "&_
"      when cast(strftime('%w', "&date&", 'start of year') as intiger) + cast(strftime('%j', "&date&") as intiger) <=8 then "&_
"          case when strftime('%Y', "&date&", '-1 year') in ('1998','2004','2009','2015') then strftime('%Y w ', "&date&", '-1 year')||'53' "&_
"               else strftime('%Y w ', "&date&", '-1 year')||'52' end "&_
"      else "&_
"         strftime('%Y w ', "&date&")||substr('0'||(((cast(strftime('%j', "&date&") as intiger) + cast(strftime('%w', "&date&", 'start of year') as intiger) - 8-1)/7)+1),-2,2) "&_
"     end "&_
" when strftime('%w', "&date&", 'start of year') = '0' then "&_
"     case "&_
"      when strftime('%j', "&date&") = '001' then "&_
"          case when strftime('%Y', "&date&", '-1 year') in ('1998','2004','2009','2015') then strftime('%Y w ', "&date&", '-1 year')||'53' "&_
"               else strftime('%Y w ', "&date&", '-1 year')||'52' end "&_
"      else "&_
"         strftime('%Y w ', "&date&")||substr('0'||(((cast(strftime('%j', "&date&") as intiger) -1-1)/7)+1),-2,2) "&_
"     end "&_
"end "
End Function

I was going to write a new one which took care of the US way of grouping weeks but now I don't have to, so thanks again ZvezdanD!

[Bex]

But I think I'll go with ISO standard and make all the weeks starts on Mondays. It might disappoint some US users and others who consider Sunday (or any other day) to be the first day of week.

hrmm, 98273498372 billion americans can't be wrong!

Quite a few others countries use Sunday as well. While some countries use Saturday and Hungary uses Wednesday!
Read about it here: (From 2002 though.)
http://www.pjh2.de/datetime/weeknumber/ ... =en#Legend

But I stumbled upon this quote regarding Americans and international standards, which I found amusing:

The great advantage of having International
Standards is that they make it easy for the
American people to differ from everybody else.

http://www.merlyn.demon.co.uk/datefmts.htm#ISO8601

[Teknojnky]

I wasn't aware (or didn't remember) about the ISO thing on mondays.

As an American, my brain is wired for sunday being the first day of the week, but Monday is the first day of the work week (ie monday thru friday).

But then again, America is also one of the few still not using the metric system either.

That quote is a good one!

[ZvezdanD]

I have actually written a piece of code which gives the ISO year & week.

I don't know what your function calculates. If you want a week of year assuming that Monday is a first day of week, your code could be much shorter:

Code: Select all

cast(strftime('%W', Date) as integer) +
case when cast(strftime('%w', Date, 'start of year') as integer) = 1
then
       0
else
       1
end

[Bex]

No, ISO year and date is much more complex than that:
http://en.wikipedia.org/wiki/ISO_week_date

[ZvezdanD]

Yeah, you are right. But, I think that you overstate with this thing. MM users would be grateful for your code without regards of ISO standards.