ISO Week Number

[Palczan]

Hello Everyone,
today, I had a problem with the Dates in TM1. Do somebody knows how to receive the iso week number? Is The function exist?
Until now I have been trying to use the following:

Code: Select all

NumberToStringEx( vDayVal , 'ww', '', '' );

but unfortunately, this function returns me a simple week number, not iso.

Does somebody know how to receive this week's num without many IF statements?

Best Regards
Palczan

[gtonkin]

Have you tried FormatDate using some of the formatting mentioned here? My guess is that it is no different to what you are getting now but worth reviewing the options.

Alternatively, I think in previous cases I added the date of the first week to an assumptions cube then used that as the seed to calculate the given week for any date I worked with.

[ascheevel]

While not a wrapped-up function, the below code will calc the ISO week for a given date. There's likely opportunity to improve & refactor this, but I was just having a bit of fun on a Friday.

Code: Select all

## temp parameter
    pDate = '20241231';
    pDateFormat = 'yyyyMMdd';

## parse date parameter
    nDate = ParseDate(pDate, pDateFormat);
    nYear = YEAR(FormatDate(nDate, 'yyyy-MM-dd'));
    sYear = NumberToString(nYear);
    sYear_Next = NumberToString(nYear + 1);


### ISO week 1 is first thursday on/after Jan 1
### last ISO week is week prior to next years week 1
    ## find weekday of 1 Jan for current & next year with Friday being 0 and Thursday 6
        sJan1 = EXPAND('%sYear%-01-01');
        nJan1 = DAYNO(sJan1);
        nJan1_Day = MOD(nJan1, 7);

        sJan1_Next = EXPAND('%sYear_Next%-01-01');
        nJan1_Next = DAYNO(sJan1_Next);
        nJan1_Day_Next = MOD(nJan1_Next, 7);

    ## calculate number of days until next Thursday (weekday 6 in TM1) and set first day of year as that date
        nTargetDay = 6;
        nDaysTillThursday = MOD((nTargetDay + 7) - nJan1_Day, 7);
        nDaysTillThursday_Next = MOD((nTargetDay + 7) - nJan1_Day_Next, 7);
        
        nFirstThursday = nJan1 + nDaysTillThursday;
        nFirstThursday_Next = nJan1_Next + nDaysTillThursday_Next;

    ## find monday date of first week
        nFirstMonday = nFirstThursday - 3;
        nFirstMonday_Next = nFirstThursday_Next - 3;

    ## if parameter date date >= nFirstMonday_Next, ISO week will be in next year's week count
        IF(nDate >= nFirstMonday_Next);
            nFirstMonday = nFirstMonday_Next;
            sYear = sYear_Next;
        ENDIF;

    ## calc ISO week of specified date by dividing difference in days between date parameter and first monday date by 7, rounding up
        nISOweek = -INT(-(nDate - nFirstMonday + 1) \ 7);

    ## write to log for testing
        sISOweek = NumberToStringEX(nISOweek, '00', '.', ',');
        LogOutput('WARN', EXPAND('%pDate% ISO Week: %sYear% W%sISOweek%'));

[Palczan]

Thanks for your Help

[Aerouge]

Hy guys, please allow me provide a short fix for ascheevels Code.

It works like a charm but in a minuscle number of dates produces problems (yeah you guessed it we were lucky to test the code on such a date).
The previous code does check wether a given date belongs to the ISO-week of the next year ... BUT it doesnt check wether it belongs to the ISO-week of a previous year. Testcase 01.01.2023 should be W52 in Year 2022 in Asheveels code it resultet in W00 in Year 2023.

Also minor hint: Set your NewDateFormatter! ... our server runs in UTC+1 and we were totally stumped to see floats show up in ndate while debugging ( )

Code: Select all

ndf = NewDateFormatter('de_de', 'Etc/UTC', 'serial', 'date');

## temp parameter
    pDate = '2023.01.01';
    pDateFormat = 'yyyy.MM.dd';

## parse date parameter
    nDate = ParseDate(pDate, pDateFormat,ndf);
    #nDate = ParseDate(pDate, pDateFormat);
    nYear = YEAR(FormatDate(nDate, 'yyyy-MM-dd'));
    sYear = NumberToString(nYear);
    sYear_Next = NumberToString(nYear + 1);
    sYear_prev = NumberToString(nYear - 1);    


### ISO week 1 is first thursday on/after Jan 1
### last ISO week is week prior to next years week 1
    ## find weekday of 1 Jan for current & next year with Friday being 0 and Thursday 6
        sJan1 = EXPAND('%sYear%-01-01');
        nJan1 = DAYNO(sJan1);
        nJan1_Day = MOD(nJan1, 7);

        sJan1_Next = EXPAND('%sYear_Next%-01-01');
        nJan1_Next = DAYNO(sJan1_Next);
        nJan1_Day_Next = MOD(nJan1_Next, 7);
        
        sJan1_prev = EXPAND('%sYear_prev%-01-01');
        nJan1_prev = DAYNO(sJan1_prev);
        nJan1_Day_prev = MOD(nJan1_prev, 7);        
        
        sDez31_prev = EXPAND('%sYear_prev%-12-31');
        nDez31_prev = DAYNO(sDez31_prev);

    ## calculate number of days until next Thursday (weekday 6 in TM1) and set first day of year as that date
        nTargetDay = 6;
        nDaysTillThursday = MOD((nTargetDay + 7) - nJan1_Day, 7);
        nDaysTillThursday_Next = MOD((nTargetDay + 7) - nJan1_Day_Next, 7);
        nDaysTillThursday_prev = MOD((nTargetDay + 7) - nJan1_Day_prev, 7);        
        
        nFirstThursday = nJan1 + nDaysTillThursday;
        nFirstThursday_Next = nJan1_Next + nDaysTillThursday_Next;
        nFirstThursday_prev = nJan1_prev + nDaysTillThursday_prev;        

    ## find monday date of first week
        nFirstMonday = nFirstThursday - 3;
        nFirstMonday_Next = nFirstThursday_Next - 3;
        nFirstMonday_prev = nFirstThursday_prev - 3;        

    ## if parameter date date >= nFirstMonday_Next, ISO week will be in next year's week count
        IF(nDate >= nFirstMonday_Next);
            nFirstMonday = nFirstMonday_Next;
            sYear = sYear_Next;
        ENDIF;
        
    ## calc ISO week of specified date by dividing difference in days between date parameter and first monday date by 7, rounding up
        nISOweek = -INT(-(nDate - nFirstMonday + 1) \ 7);        
        
    ## Check wether the current date belongs to the previous year
        IF(nDate < nFirstMonday);
            sYear = sYear_prev;
            nISOweek = -INT(-(nDate - nFirstMonday_prev + 1) \ 7);                
        ENDIF;        

    ## write to log for testing
        sISOweek = NumberToStringEX(nISOweek, '00', '.', ',');
        LogOutput('WARN', EXPAND('%pDate% ISO Week: %sYear% W%sISOweek%'));

[ascheevel]

Oof, I indeed missed handling for when date is in prior year's ISO week count. Good catch, +1 for using NewDateFormatter, and thank you for posting updated code.

A couple thoughts:

As much as I like a Dez variable for Deutsch Dezember, I don't think these variables are necessary and can be removed.

Code: Select all

sDez31_prev = EXPAND('%sYear_prev%-12-31');
nDez31_prev = DAYNO(sDez31_prev);

The two IF statements can be combined with an ELSEIF and remove duplication of nISOWeek formula
original:

Code: Select all

## if parameter date date >= nFirstMonday_Next, ISO week will be in next year's week count
    IF(nDate >= nFirstMonday_Next);
        nFirstMonday = nFirstMonday_Next;
        sYear = sYear_Next;
    ENDIF;
    
## calc ISO week of specified date by dividing difference in days between date parameter and first monday date by 7, rounding up
    nISOweek = -INT(-(nDate - nFirstMonday + 1) \ 7);        
    
## Check wether the current date belongs to the previous year
    IF(nDate < nFirstMonday);
        sYear = sYear_prev;
        nISOweek = -INT(-(nDate - nFirstMonday_prev + 1) \ 7);                
    ENDIF;

updated:

Code: Select all

## if parameter date date >= nFirstMonday_Next, ISO week will be in next year's week count
## if parameter date < nFirstMonday, ISO week will be in previous year's week count
    IF(nDate >= nFirstMonday_Next);
        nFirstMonday = nFirstMonday_Next;
        sYear = sYear_Next;
    ELSEIF(nDate < nFirstMonday);
        nFirstMonday = nFirstMonday_prev;
        sYear = sYear_prev;
    ENDIF;
    
## calc ISO week of specified date by dividing difference in days between date parameter and first monday date by 7, rounding up
    nISOweek = -INT(-(nDate - nFirstMonday + 1) \ 7);

Give it a test and post back with full code if you like the changes and of course any further bug fixes

[Wim Gielis]

Hello,

I thought I would test this, and it works fine
I made a couple of smaller edits. Here is the code for 1 date, output in the TM1 server log:

Code: Select all

pDate = '20241231';
pDateFormat = 'yyyyMMdd';
ndf = NewDateFormatter( 'nl_BE', 'Etc/UTC', 'serial', 'date' );

# parse the date parameter
nDate = ParseDate( pDate, pDateFormat, ndf );
sYear = FormatDate( nDate, 'y' );
sYear_Next = NumberToString( StringToNumber( sYear ) + 1 );

# ISO week 1 is the first Thursday on/after Jan 1
# last ISO week is the week prior to next years week 1
# find the weekday for Jan 1 for the current and next year, where Friday is 0 and Thursday is 6
nJan1 = Dayno( Expand( '%sYear%-01-01' ));
nJan1_Day = Mod( nJan1, 7 );

nJan1_Next = Dayno( Expand( '%sYear_Next%-01-01' ));
nJan1_Day_Next = Mod( nJan1_Next, 7 );

# calculate number of days until next Thursday (weekday 6 in TM1) and set first day of year as that date
nTargetDay = 6;
nDaysTillThursday = Mod( ( nTargetDay + 7 ) - nJan1_Day, 7 );
nDaysTillThursday_Next = Mod( ( nTargetDay + 7 ) - nJan1_Day_Next, 7 );

nFirstThursday = nJan1 + nDaysTillThursday;
nFirstThursday_Next = nJan1_Next + nDaysTillThursday_Next;

# find Monday's date of the first week
nFirstMonday = nFirstThursday - 3;
nFirstMonday_Next = nFirstThursday_Next - 3;

# if parameter date date >= nFirstMonday_Next, ISO week will be in next year's week count
If( nDate >= nFirstMonday_Next );
   nFirstMonday = nFirstMonday_Next;
   sYear = sYear_Next;
EndIf;

# calculate the ISO week of specified date by dividing difference in days between date parameter and first monday date by 7, rounding up
nISOweek = -Int( -( nDate - nFirstMonday + 1 ) / 7 );

# write to log for testing
sISOweek = NumberToStringEx( nISOweek, '00', '', '' );
LogOutput( 'INFO', Expand( 'ISO Week for %pDate%: %sYear% W%sISOweek%' ));

Here is the code for a window of over 11 years of dates, output in a text file - less edited (I prefer my formatting):

Code: Select all

DatasourceASCIIQuoteCharacter = '';
ndf = NewDateFormatter( 'nl_BE', 'Etc/UTC', 'serial', 'date' );

# loop over more than 10 years, day by day
dLoop = Dayno( '2019-12-15');
While( dLoop <= Dayno( '2031-01-15' ));

   # yyyy-mm-dd
   pDate = Date( dLoop, 1);
   pDateFormat = 'yyyy-MM-dd';

## parse the date parameter
    nDate = ParseDate(pDate, pDateFormat,ndf);
    nYear = YEAR(FormatDate(nDate, 'yyyy-MM-dd'));
    sYear = NumberToString(nYear);
    sYear_Next = NumberToString(nYear + 1);
    sYear_prev = NumberToString(nYear - 1);


### ISO week 1 is first thursday on/after Jan 1
### last ISO week is week prior to next years week 1
    ## find weekday of 1 Jan for current & next year with Friday being 0 and Thursday 6
        sJan1 = EXPAND('%sYear%-01-01');
        nJan1 = DAYNO(sJan1);
        nJan1_Day = MOD(nJan1, 7);

        sJan1_Next = EXPAND('%sYear_Next%-01-01');
        nJan1_Next = DAYNO(sJan1_Next);
        nJan1_Day_Next = MOD(nJan1_Next, 7);

        sJan1_prev = EXPAND('%sYear_prev%-01-01');
        nJan1_prev = DAYNO(sJan1_prev);
        nJan1_Day_prev = MOD(nJan1_prev, 7);

    ## calculate number of days until next Thursday (weekday 6 in TM1) and set first day of year as that date
        nTargetDay = 6;
        nDaysTillThursday = MOD((nTargetDay + 7) - nJan1_Day, 7);
        nDaysTillThursday_Next = MOD((nTargetDay + 7) - nJan1_Day_Next, 7);
        nDaysTillThursday_prev = MOD((nTargetDay + 7) - nJan1_Day_prev, 7);

        nFirstThursday = nJan1 + nDaysTillThursday;
        nFirstThursday_Next = nJan1_Next + nDaysTillThursday_Next;
        nFirstThursday_prev = nJan1_prev + nDaysTillThursday_prev;

    ## find monday date of first week
        nFirstMonday = nFirstThursday - 3;
        nFirstMonday_Next = nFirstThursday_Next - 3;
        nFirstMonday_prev = nFirstThursday_prev - 3;

## if parameter date date >= nFirstMonday_Next, ISO week will be in next year's week count
## if parameter date < nFirstMonday, ISO week will be in previous year's week count
    IF(nDate >= nFirstMonday_Next);
        nFirstMonday = nFirstMonday_Next;
        sYear = sYear_Next;
    ELSEIF(nDate < nFirstMonday);
        nFirstMonday = nFirstMonday_prev;
        sYear = sYear_prev;
    ENDIF;
    
## calc ISO week of specified date by dividing difference in days between date parameter and first monday date by 7, rounding up
    nISOweek = -INT(-(nDate - nFirstMonday + 1) \ 7);

    ## write to log for testing
sISOweek = NumberToStringEx( nISOweek, '00', '', '' );
AsciiOutput( 'INFO_' | GetProcessName | '.txt', Expand( 'ISO Week for %pDate%: %sYear% W%sISOweek%' ));

   dLoop = dLoop + 1;

End;

The full 11-year loop coincides with the Excel results of the function ISOWEEKNUM. Good job both.