LastWorkDate and NetWorkDays excluding Sunday and holiday

[#1]

Hello,

I need to calculate the working days between two dates excluding only Sunday and holidays. The problem is that the function NetWorkDays  in qlikview always consider Saturday as non working days.

I need to do the same to calculate the last work date including Saturday but excluding Sunday and holidays.

I've to use it in an expression, I've just tried the following expression but it does not consider if there is a saturday which is holiday:

NetWorkDays(Today(),YearEnd(Today()),$(vPublicHolidays))+
(FLOOR((YearEnd(Today())-Today())/7)+IF(WEEKDAY(Today())+MOD(YearEnd(Today())-Today (),7)>=5,1,0))

I wasn't able to find a solution for LastWorkDate function including Saturday.

I've already read this link Master Calendar with Working days & Holidays Flag but I don't know how can I use it in my case.

Any ideas?

Thank you very much for any suggestions.

[Anonymous]

in frontend or script?

[beck_bakytbek]

Hi Flavio,

look at my attached example, it is an example, where you can calculate the networking day with excluding of Sunday and Holiday

i hope that helps

Beck

[#4]

Hi Robin,

I need it in frontend, not in load script

[#5]

Hi beck110979‌

Thanks for yout feedback in you example you are using NetWorkDays function that excludes Saturday and Sunday, am I wrong?

I need to do this calculation in front end and not in load script.

Thank you,

Flavio

[stanlyrj]

Hi Beck,

I have gone through your example. Its calculating the working days excluding Saturday,Sunday, and public holidays.
Could you please explain how I can calculate working days by excluding Sundays and Holidays only.

Thank You

Stan

[beck_bakytbek]

Hi Stanly,

try this:

Holiday:

load

Date(Date#(MyDate, 'DD.MM.YYYY')) as MyDate

Inline [

MyDate,

01.05.2017

06.05.2017

07.05.2017

13.05.2017

14.05.2017

20.05.2017

21.05.2017

29.04.2017

30.04.2017

15.04.2017

13.04.2017

14.04.2017

17.04.2017

];

ConcatTemp:

Load

Concat(Chr(39) & MyDate & Chr(39), ',') as HolidayDays

Resident Ferien;

Let vPublHoliday = FieldValue('HolidayDays',1);

Let vCurrentMonth = Month(Today());

MySales:

Load

Date(Date#(SalesDate, 'DD.MM.YYYY')) as SalesDate, Sales

Inline [

SalesDate, Sales

03.05.2017, 230

04.05.2017, 25

16.05.2017, 66

10.05.2017, 120

19.05.2017, 32

12.04.2017, 100

05.04.2017, 12

03.06.2017,88

];

Sales:

Load *,

Month(SalesDate) as  MyMonth,

MonthName(SalesDate) as MyMonthYear,

if(Year(SalesDate) = Year(Today()) and Month(SalesDate) = Month(Today()),

NetWorkDays(MonthStart(Today()),(Today()),$(vPublHoliday)),NetWorkDays(MonthStart(SalesDate),MonthEnd(SalesDate),

$(vPublHoliday))) as MyWorkingDays

Resident MySales;

drop Table MySales, Holiday;

if you create on UI the straight table, you can see 03.06.2017, and this Day is Saturday.

I hope that helps, if you have any question, let me please know,

Beck

[stanlyrj]

Hi Beck,

Thanks for your feedback, I have used a different script to solve the issue. Could you please review it once.

Script

If(WeekDay(Date(DateNumber)) <> 6 and not match(Date(DateNumber),$(vPublicHolidays)),'Working','Not Working') as WorkingDayFlag

Expression

=Count({$<$(vSet_YTD)>}WorkingDayFlag)

Thank you

Stan

[beck_bakytbek]

Hi Stanly,

did you use my above suggestion?

Beck

[stanlyrj]

Hi Beck,

I have used some lines from your scripts
Please see the attached .qvs file.

Thank You
Stan