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.

[beck_bakytbek]

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

[stanlyrj]

Hi Beck,

Thank You, Issue solved.

Thank You

Stan

[beck_bakytbek]

if you solved this issue,

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

[stanlyrj]

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