topic Re: Exclude Weekends and Public Holiday in QlikView

Exclude Weekends and Public Holiday

Wed, 25 Nov 2020 16:16:04 GMT

Good Morning,

PFA

I have attached QV app with Leave Date and Public holiday data. The app calculates number of leave days for a specific period and it works fine. However I want to exclude weekends and public holidays as well.

Please help.

Regards

Re: Exclude Weekends and Public Holiday

ramasaisaksoft — Wed, 28 Dec 2016 09:33:36 GMT

networkdays() is a function

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

networkdays (startdate, enddate, holidaydate1, holidaydate2,holidaydate3,holidaydate4.......)

for example

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26')

Re: function:networkday

just go through John attachment .qvw file also.

Re: Exclude Weekends and Public Holiday

raajaswin — Wed, 28 Dec 2016 09:44:31 GMT

Do you want to generate a holiday list?? If yes then do as below

LET vDatMi = Num(MakeDate(2016,1,1));

LET vDatMa = Num(Today());

CAL1:

LOAD

Date($(vDatMi) + IterNo()) AS ALL_DATE

AUTOGENERATE 1

WHILE $(vDatMi)+IterNo() <= $(vDatMa);

HOLIDAY_WEEKEND:

LOAD

if(match(weekday(ALL_DATE),'Sat','Sun'),ALL_DATE) as Holidays

Resident CAL1;

Drop Table CAL1;

Concatenate

PUBLIC_HOLIDAY_LIST:

//Load your public holiday list here

This will give you a consolidated Holiday list with weekends and Public holidays

Re: Exclude Weekends and Public Holiday

raajaswin — Wed, 28 Dec 2016 09:47:08 GMT

If you want to exclude these holidays.. you can use where not exists() with a resident and achieve this

Re: Exclude Weekends and Public Holiday

Wed, 28 Dec 2016 09:51:48 GMT

Hi Aswin,

So the code you provided me with is basically the same as the one which I have in model provided? So the only change which I have to add is :

HOLIDAY_WEEKEND:

LOAD

if(match(weekday(ALL_DATE),'Sat','Sun'),ALL_DATE) as Holidays

Resident CAL1;

Drop Table CAL1;

Concatenate

PUBLIC_HOLIDAY_LIST:

//Load your public holiday list here

Re: Exclude Weekends and Public Holiday

raajaswin — Wed, 28 Dec 2016 10:01:53 GMT

May b, try doing that. or provide the app with that sample data to reload