This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
QlikView documentation and resources.
A very important function to use to get the number of days and remove the weekends and holidays.
networkdays (start_date, end_date [, holiday])
networkdays ('19/12/2013', '07/01/2014', '25/12/2013', '26/12/2013')
Returns 12. This example takes the holiday 25/12/2013 to 26/12/2013 into account.
Reference : https://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/networkd...
So we need to write all holidays dates in syntax?
yes it seems so based on the example found in the reference link above.
Load * Inline
CHR(39) & Concat(HolidayDate,Chr(39)&','&Chr(39)) & CHR(39) as ConcatHolidays
Drop Table Holidays;
Let vHolidays = Peek('ConcatHolidays',0,'ConcatHolidays');
Drop Table ConcatHolidays;
Load all holidays from a database or excel file, concatenate them and create a variable.
Now you can use vHolidays variable inside networkdays function.
// Calculating NetWorking Days (Excludidng Saturdays, Sundays and Public Holidays(UK))
Let vYearStart =NUM(YearStart(Today(),-1,4));
Let vYearEnd = NUM(YearEnd(Today()));
Date($(vYearStart) + rowno() -1) as Date
AUTOGENERATE $(vYearEnd)- $(vYearStart)+1;
Load * INLINE
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
New Year's Day, 01/01/2014
Good Friday, 18/04/2014
Easter Monday, 21/04/2014
Early May Bank Holiday, 05/05/2014
Spring Bank Holiday, 26/05/2014
Summer Bank Holiday, 25/08/2014
Christmas Day, 25/12/2014
Boxing Day, 26/12/2014
New Year's Day, 01/01/2015
Good Friday, 03/04/2015
Easter Monday, 06/04/2015
Early May Bank Holiday, 04/05/2015
Spring Bank Holiday, 25/05/2015
Summer Bank Holiday, 31/08/2015
Christmas Day, 25/12/2015
Boxing Day, 28/12/2015
New Year's Day, 01/01/2016
Good Friday, 25/03/2016
Easter Monday, 28/03/2016
Early May Bank Holiday, 02/05/2016
Spring Bank Holiday, 30/05/2016
Summer Bank Holiday, 29/08/2016
Christmas Day, 26/12/2016
Boxing Day, 27/12/2016
LOAD concat(chr(39) & HDate & chr(39),',') as HolidayDates
Let vPublicHolidays = fieldvalue('HolidayDates',1);
DROP TABLE tmpHoliday;
DROP TABLE tmpConcat;
Kind of a bummer this formula doesn't work like the one in Excel where you can specify a column name in the Holiday parameter instead of having to use a CSV.
Soooo, nobody wrote a script yet that calculates the most common holidays each year?
The basic ones are easy; Christmas and New Year, but did anyone try their hands yet on a few of the others?
... and after just looking into the math needed to calculate Easter, I totally understand why nobody might have bothered yet. I guess this will become another project some day.