Working Days per month for year 2013

[MK_QSL]

Hi,

I have below list of holidays for year 2013.

How can we get table showing Month and No of Working Days for Year 2013? i.e. Monday to Friday.

Also, what if Alternate Saturday also count as half Working Day?

Holiday	Date
New Year's Day	01/01/2013
Good Friday	29/03/2013
Easter Monday	01/04/2013
Early May Bank Holiday	06/05/2013
Spring Bank Holiday	27/05/2013
Summer Bank Holiday	26/08/2013
Christmas Day	25/12/2013
Boxing Day	26/12/2013

[ariel_klien]

Hi,

LET vDateMin = Num(MakeDate(2012,1,1)); 
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());  
TempCalendar:
LOAD  $(vDateMin) + RowNo() - 1 AS DateNumber, 
            Date($(vDateMin) + RowNo() - 1) AS TempDate AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= Today(); 
MasterCalendar: 
LOAD    TempDate AS CalendarDate,  
               Day(TempDate) AS CalendarDay,    
               WeekDay(TempDate) AS CalendarWeekDay,   
               Week(TempDate) AS CalendarWeek,
               IF (WeekDay(TempDate)='5' OR WeekDay(TempDate)='6','Weekend','Weekday') as Day_Type,   
               Month(TempDate) AS CalendarMonth,  
               Year(TempDate) AS CalendarYear, 
               'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,    
               WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,   
               Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear RESIDENT TempCalendar ORDER BY TempDate ASC; 
DROP TABLE TempCalendar;  

BR

Ariel

[MK_QSL]

Dear Ariel,

Thansk but this is not what I want.

I want a table showing month name and number of working days for particular month.

[ariel_klien]

Hi

Please see the attachment

Is that what you meant?

BR

Ariel

[Gysbert_Wassenaar · Accepted solution]

You can use the networkdays function to calculate workdays. It takes a start and an end date and optionally a comma separated list of holiday dates.

networkdays(monthstart(MyDate),monthend(MyDate), ...holidayshere... )

networkdays(makedate(Year,Month),monthend(makedate(Year,Month)), ...holidayshere... )

It's probably easiest to create a variable that contains the list of holidays and use that variable in the networkdays function.

Holidays:

LOAD Holiday, Date#(Date,'DD-MM-YYYY') as Date INLINE [

    Holiday, Date

    "New Year's Day", 01-01-2013

    Good Friday, 29-03-2013

    Easter Monday, 01-04-2013

    Early May Bank Holiday, 06-05-2013

    Spring Bank Holiday, 27-05-2013

    Summer Bank Holiday, 26-08-2013

    Christmas Day, 25-12-2013

    Boxing Day, 26-12-2013

];

Temp:

LOAD concat(num(Date), ',') as HolidayList

resident Holidays;

LET vHolidays = peek('HolidayList');

drop Table Temp;

In the expression in the chart/textbox/whatever:

networkdays(makedate(Year,Month),monthend(makedate(Year,Month)), vHolidayList )

edit: attached an example

[Anonymous]

I usually keep the list of holidays in a variable H.  So in this case the chart expression is

count({<Date-={$(H)}>} if(weekday(Date)<5,Date))

And dimension is Month-Year

[MK_QSL]

Dear Gysbert,

Upto creating variable, understoond everything but can you prepare a chart having month name and no of working days for me please?

[MK_QSL]

any reply on this?

[MK_QSL]

Excellent example.

Thanks for your help....