Calculate Number of Days Since/Until specified weekday

Ideas and tips for enhancing your TM1 application
Post Reply
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Calculate Number of Days Since/Until specified weekday

Post by ascheevel »

I've been meaning to share this for a while. The recent post by Palczan spurred me to finally post this.

A few times I've needed to find either the next or previous specified weekday (often a Monday) from a given date and save as attribute. Below is useful math for determining next/previous specified weekday without need for WHILE loop.

When you're not using excel serial date, the date origin in TM1 is 1 Jan 1960, a Friday. Most of us are aware that we can use MOD(DAYNO(TODAY),7) or MOD(INT(NOW()),7) to return the numeric day of week where Friday is 0 and Thursday is 6.

If you want to determine the number of days until the next Monday (weekday number 3) for example, you could use the code below. You could then add the result to the current DAYNO to get the date serial for that Monday.

Code: Select all

## calc number of days until next Monday where Monday is weekday 0
    pTargetDay = 3;
    nToday = DAYNO(TODAY);
    nTodayWeekday = MOD(nToday, 7);
    nDividend = (pTargetDay + 7) - nTodayWeekday;
    
    nDaysUntil = MOD(nDividend, 7);
    nNextMondaySerial = nToday + nDaysUntil;
    sToday = DATE(nToday);
    sNextMonday = DATE(nNextMondaySerial);
    LogOutput('WARN', EXPAND('Todays date: %sToday%; NEXT Mondays date: %sNextMonday%'));

The below code would give us the number of days since the last Monday as example. The nDaysSince formula is a bit more complex than the nDaysUntil above because it seems TM1 will return the truncated modulo instead of the floored modulo like we would get in excel when the dividend is negative. I've included the commented out formula that would work in excel for reference.

Code: Select all

## calc number of days since last Monday where Monday is weekday 0
    pTargetDay = 3;
    nToday = DAYNO(TODAY);
    nTodayWeekday = MOD(nToday, 7);
    nDividend = nTodayWeekday - (pTargetDay + 7);

    # below only works in excel where the floored modulo is computed instead of truncated like in TM1
    # nDaysSince = MOD(nDividend, 7);
    
    # below achieves floored modulo in TM1
    nDaysSince = nDividend - (7 * INT(nDividend \ 7));
    nLastMondaySerial = nToday - nDaysSince;
    sToday = DATE(nToday);
    sLastMonday = DATE(nLastMondaySerial);
    LogOutput('WARN', EXPAND('Todays date: %sToday%; LAST Mondays date: %sLastMonday%'));
Post Reply