Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

LastWorkDate and NetWorkDays excluding Sunday and holiday

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.


Tags (2)
13 Replies
robin_hausdoerfer
Valued Contributor III

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

in frontend or script?

beck_bakytbek
Honored Contributor

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

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

Not applicable

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

Hi Robin,

I need it in frontend, not in load script

Not applicable

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

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
New Contributor III

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

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
Honored Contributor

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

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
New Contributor III

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

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
Honored Contributor

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

Hi Stanly,

did you use my above suggestion?

Beck

stanlyrj
New Contributor III

Re: LastWorkDate and NetWorkDays excluding Sunday and holiday

Hi Beck,

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


Thank You
Stan