topic Re: Working Days per month for year 2013 in QlikView

Working Days per month for year 2013

MK_QSL — Wed, 03 Jul 2013 14:26:41 GMT

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

Re: Working Days per month for year 2013

ariel_klien — Wed, 03 Jul 2013 14:37:11 GMT

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;

Ariel

Re: Working Days per month for year 2013

MK_QSL — Wed, 03 Jul 2013 14:43:31 GMT

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.

Re: Working Days per month for year 2013

ariel_klien — Wed, 03 Jul 2013 14:51:15 GMT

Please see the attachment

Is that what you meant?

Ariel

Re: Working Days per month for year 2013

Gysbert_Wassenaar — Wed, 03 Jul 2013 14:53:55 GMT

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

Re: Working Days per month for year 2013

Anonymous — Wed, 03 Jul 2013 14:59:38 GMT

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

Re: Working Days per month for year 2013

MK_QSL — Wed, 03 Jul 2013 15:02:55 GMT

Dear Gysbert,

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

Re: Working Days per month for year 2013

MK_QSL — Wed, 03 Jul 2013 15:48:01 GMT

any reply on this?

Re: Working Days per month for year 2013

MK_QSL — Wed, 03 Jul 2013 19:03:37 GMT

Excellent example.

Thanks for your help....