topic LastWorkDate and NetWorkDays excluding Sunday and holiday in QlikView

LastWorkDate and NetWorkDays excluding Sunday and holiday

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

Anonymous — Wed, 26 Apr 2017 16:34:54 GMT

in frontend or script?

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

beck_bakytbek — Wed, 26 Apr 2017 20:06:27 GMT

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

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

Thu, 27 Apr 2017 06:16:23 GMT

Hi Robin,

I need it in frontend, not in load script

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

Thu, 27 Apr 2017 06:21:28 GMT

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

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

stanlyrj — Wed, 31 May 2017 16:57:51 GMT

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

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

beck_bakytbek — Thu, 01 Jun 2017 07:25:14 GMT

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

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

stanlyrj — Thu, 01 Jun 2017 11:39:33 GMT

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

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

beck_bakytbek — Thu, 01 Jun 2017 12:03:56 GMT

Hi Stanly,

did you use my above suggestion?

Beck

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

stanlyrj — Thu, 01 Jun 2017 12:13:20 GMT

Hi Beck,

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

Thank You
Stan

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

beck_bakytbek — Thu, 01 Jun 2017 12:17:53 GMT

Hi Stanly

Use these steps (with variables)

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;

Beck

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

stanlyrj — Thu, 01 Jun 2017 15:02:46 GMT

Hi Beck,

Thank You, Issue solved.

Thank You

Stan

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

beck_bakytbek — Thu, 01 Jun 2017 15:28:23 GMT

if you solved this issue,

you can post the solution, so other colleagues can participate on it

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

stanlyrj — Fri, 02 Jun 2017 10:34:17 GMT

Hi Flavo,

The following script may be helpful to you to find the solution for your problem.

Place all the holiday dates into an excel file.

Master Calendar Calculation Script. Use your date field in this script.

HolidayTmp:

LOAD Date

FROM

[.\Import\Holidays.xlsx]

(ooxml, embedded labels, table is Holidays);

ConcatTmp:

LOAD Concat (chr(39) & Date & chr(39),',') as HolidayDates

Resident HolidayTmp;

Let vPublicHolidays = FieldValue('HolidayDates',1);

Let vCurMonth = Month(Today());

Dates:

LOAD date(FieldValue('$(DateField)', RecNo())) AS TempDates

AUTOGENERATE FieldValueCount('$(DateField)')

;

Dates_Range:

LOAD

Max(TempDates) AS MaxDate,

Min(TempDates) AS MinDate

Resident Dates

;

Drop table Dates;

LET vDateMin = Num(Peek('MinDate', 0, 'Dates_Range'));

LET vDateMax = Num(Peek('MaxDate', 0, 'Dates_Range'));

DROP TABLE Dates_Range;

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)

;

Calendar:

LOAD

DateNumber AS $(DateField),

Date(DateNumber) AS Date,

Day(DateNumber) AS Day,

Weekday(DateNumber) AS WeekDay,

Week(DateNumber) AS Week,

Month(DateNumber) AS Month,

MonthName(DateNumber) AS MonthYear,

'Q' &Ceil(Month(DateNumber)/3) AS Quarter,

'S' &Ceil(Month(DateNumber)/6) AS Semester,

Year(DateNumber) AS Year,

If(WeekDay(Date(DateNumber)) <> 6 and not match(Date(DateNumber),$(vPublicHolidays)),1,0) as WorkingDayFlag

RESIDENT TempCalendar

ORDER BY DateNumber ASC

;

//total working days per month calculation

left join(Calendar)

Load

MonthYear,

sum(WorkingDayFlag) as TotalWorkingDays

resident Calendar group by MonthYear;

//Accumulated working days calculation

left join(Calendar)

Load

Date,

MonthYear,

if(MonthYear=peek(MonthYear), peek(WorkingDay) + WorkingDayFlag, WorkingDayFlag) as WorkingDay

resident Calendar

ORDER BY Date ASC

;

DROP TABLE TempCalendar,HolidayTmp;

The output will be something like this:

Month Natural Day Working Day Total Working Days

jun 1 1 26

jun 2 2 26

jun 3 3 26

jun 4 3 26

jun 5 4 26

jun 6 5 26

jun 7 6 26

jun 8 7 26

jun 9 8 26

jun 10 9 26

jun 11 9 26

jul 12 10 26

Hope this script will be helpful to you.

Regards

Stan