Date Difference excluding weekends

[Mark RMBC]

Hi all,

I am wondering if I have missed something along the way and am about to embarrass myself, but here goes anyway.

I am trying to come up with a networkingdays formula, for the time being I am trying to calculate the difference between 2 dates excluding any weekends (I will get to holidays when I have the weekends sorted). Also I have not thought about if the start or end date is a weekend!

Now I have come up with a methodology which is below, but I am wondering if anyone has anything better? Feel free to find holes in my method!

Code: Select all

# The start date
vDate1 = '2017-06-15';
# Serial number of start date
vDateSerial1 = Dayno(vDate1);
# The day of the week of the start date
vDayOfWeek1= Mod ( vDateSerial1 + 21915, 7);
# The date of Monday in the same week as the start date
vMonDate = date(vDateSerial1 - (vDayOfWeek1 -1), 1);
# The end date
vDate2 = '2017-06-30';
# Serial number of end date
vDateSerial2 = Dayno(vDate2);
# The day of the week of the end date
vDayOfWeek2 = Mod ( vDateSerial2 + 21915, 7);
# The date of Friday in the same week as the end date
vFriDate = date(vDateSerial2 + (5-vDayOfWeek2), 1);
# The number of weeks between the Monday and Friday dates multiplied by 2 for the number of weekends and minus 2 for the 2 weekends in the last week
vWeeks = Round(((DayNo(vFriDate) - DayNo(vMonDate))/7) + 0.49999) *2-2;
# The difference between the Start and End date minus the weekends
vDatDiff = (vDateSerial2 - vDateSerial1 +1) - vWeeks;

cheers, Mark

[gtonkin]

Not sure if it is better but different-I normally have a Calendar cube with Calendar Days and Business Days as Measures, Year, Month and Days as other dimensions. Calendar Days have 1 against each applicable day so that I can get to a total per month and then per year. I can then also configure Feb with 28/29 as required.
Business days are then populated based on a similar metholodgy to yours i.e. flag work days. We then unflag any holidays and update through the year as new ones are announced. From a rule/TI point of view it is fairly simple to read and get the desired value.
HTH

[PavoGa]

I use a calendar cube as well. I can include whatever measures are needed and not have to worry about coding formulas throughout everything. Mine includes remaining working days for the period, year, etc. I did take the lazy way out by building the measures in an Excel sheet and then importing them.

[Mark RMBC]

Thanks for the response guys.

I guess a Calendar cube makes sense because it allows for greater control and is more in the TM1 spirit!

However one small problem, I am struggling to imagine what the fairly simple solution to get the networkingdays from this set up might be. Other than in the TI doing a loop through the day dimension, getting the Business Day value and incrementing as I loop.

I suspect the solution is simpler than that?!

I would be interested to know how the rule would look too!

cheers, Mark

[PavoGa]

It depends on the metric you need with regards to the solution, but here is one example:

Say I need to know for a given date, the remaining workdays in the month. I have a RemainingWorkDaysInTheMonth measure, so a CELLGETN retrieves that from the cube. Same solution for remaining days in a Qtr, Year, etc at a given date. Now for the working days between two given dates...you could build a subset and loop through it adding working days OR the solution may include having consolidations for Quarters, Years, etc. Your measures would include what quarter, year, quarter-year a day belongs to and you may do multiple CELLGET's to get the numbers.

Other useful measures may be WorkDaysPeriodToDate or ...QTD or ...YTD. So to calculate the number of workdays within a year, you look up the respective metric for two given dates and calculate the difference.

Someone may have a more elegant solution on the number of days between two randomly picked days, I've just not had to deal with it. I'd still like to see it though.

[Mark RMBC]

Hi,

Well I have created a cube with Years, Days and Day measures dimensions. (I will probably end up with more dimensions and/or hierarchies within the dimensions)

The day measures include Day of the Week (Monday, Tuesday etc) and a Business Day flag (0 or 1). The Business day flag is a rule which says if the day of the week is Sunday, Saturday or blank then 0 else 1.
Against days I have created a Day of Year attribute, with 1 being 01-Jan and 366 being 31-Dec.

With this set up I have been able to create a TI process which loops through the Year dimension and the Days dimension and returns the Business Day value as it loops adding this value to a date difference variable, and restricting to only those dates that fall between the start and end date.

For information the TI code is shown below (Which while not rocket science does not fall under my idea of a fairly simple solution, so maybe I am missing something?).

I can see how this set up, i.e. creating a Cube, allows for more measures to be added and easily allows holidays to be turned on and off. So definitely an improvement on simply doing it all in a TI on an ad hoc basis.

I can’t yet see how a rule could possibly be created to find the difference between 2 dates and exclude weekends, holidays etc?

It would be nice if TM1 had more extensive functionality as with some other languages!

Update: I have added this coding to a load process and all the date differences are showing correctly. Just for context the model I am creating is for an insurance scheme that covers employee absence from work (among other things), and the days they are absent drives lots of calculations.

Code: Select all

## All on prolog for now!

# File Path for Asciioutput testing

FilePath ='XXXXXXX\';
File = GetProcessName();

# Define Start and End dates

vYearS = '2017';
vYearE = '2019';
vDayS = '15-Jun';
vDayE = '04-Jul';

# Define Loop variables

DimName = '010 Main - Days';
DimNameYr = '002 Main - Years';

vDaySDOY = AttrN(DimName, vDayS, 'Day of Year');
vDayEDOY = AttrN(DimName, vDayE, 'Day of Year');

vCube = 'Weekday';

LoopTotYr = Dimsiz(DimNameYr);
LoopTot = Dimsiz(DimName);

vDateDiff = 0;

x=1;
Y=1;

While (x< LoopTotYr);

elYr = DIMNM(DimNameYr,x);

If(DTYPE(DimNameYr, elYr) @='N');

If(Numbr(elYr) >= Numbr(vYearS) & Numbr(elYr) <= Numbr(vYearE));

While (y<=LoopTot);

el = DIMNM(DimName, y);

eldayno = Attrn(DimName, el, 'Day of Year');

If(el @<>'All Days');
If(vYearS @= vYearE);
If(eldayno >=vDaySDOY & eldayno <=vDayEDOY);

vBusDay = cellgetn(vCube, elYr, el, 'Business Day');

EndIf;

else;

If( (elYr @= vYearS & eldayno >=vDaySDOY) % (Numbr(elYr) > Numbr(vYearS) & Numbr(elYr) < Numbr(vYearE)) % (elYr @= vYearE & eldayno <=vDayEDOY));

vBusDay = cellgetn(vCube, elYr, el, 'Business Day');

EndIf;

Endif;

Endif;

vDateDiff = vDateDiff + vBusDay;

AsciiOutput( FilePath | File | '.csv', elYr, el, numbertostring(vBusDay), numbertostring(vDateDiff));

y=y+1;

vBusDay = 0;

End;

endif;
endif;

y=1;

x=x+1;

End;

[declanr]

Just throwing another option in the mix.
Depending how often and in what fashion you want to access the data; you could have 2 flat dims for dates (identical to each other) and a measured dim. Then use TI to populate the intersections between date from and date to for things such as "working days".

Then you only need to run the TI once (or as often as your knowledge of working days etc changes) and you have an easy cube to reference in either TI or rules with no real further calculation.

[gtonkin]

I like Declan's suggestion for simplicity-reminds me of those triangulation distance charts in map books showing distance between each city and every other city-same logic.

If you do want to go down the rules road, and based on you recent posting in terms of tracking leave, multi-year adds some additional complexity. This is what I have in a model currently:
Year, Month and Day dimension along with the measure per my original post.
Year => flat list of years
Month => Contains Months and Rollups to YTDs i.e. Dec YTD contains all months. Attribute for PriorMonth.
Day => Contains each day - 01 to 31 and Rollups for each day i.e. 01 MTD, 02 MTD,...,31 MTD (All Days contains same as 31-my model also has day 00 for populated this month, relates to historic month)

I then have a rule to calculate the difference e.g.:

Code: Select all

SKIPCHECK;
['Days']=N:
DB('sp-Calendar',!sp-Year,ATTRS('sp-Month',!sp-Month,'PriorMonth')|' YTD','31 MTD','Business Day')+
DB('sp-Calendar',!sp-Year,!sp-Month,!sp-Day|' MTD','Business Day')-
DB('sp-Calendar','2017','Jan','01','Business Day');

Note: Just hardcoded the 2017-Jan-01 but could obviously be a cube reference. Created a blank cube for explanantion.
So basically take the YTD days as at last month and add the days for this month.

Hope this helps - at least in deciding on how to proceed.

[Mark RMBC]

Hi,

Appreciate the responses.

I hadn't thought of using declan’s approach, which does seem the simplest solution, very slightly complicated by multi-year calculations. I have tried it and it works fine and certainly simplifies the TI, I only now need to account for if the networkingdays spans years.

Gtonkin,

Have not tested your method yet but the penny is slowly dropping I think!

Your method made another idea spring to mind, you can have one date dimension and add the days cumulatively but ignore non business days, so 01/01/2017 is 0, 02/01/2017 is 1, 03/01/2017 is 2 etc etc etc. This way the formula is simply the end date – start date. So for example if Start date is 15th June and End date is 4th July the formula becomes 133-120+1 = 14. Again the only minor complication is when you span years but this is easily resolved by taking 31st Dec from the Start date and taking the End date from 1st Jan and add the 2 together.

The good thing about the cube solutions is that the intersections can be easily populated using excel formulas!

Thanks again!