# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a \$200 Amazon Gift Card! Watch Video
cancel
Showing results for
Did you mean:
Creator

## QLIKview networkdays ignore weekend

Hi,

How can I create networkdays by ignore weekend as holidays? All holidays (weekend and Public Holiday) will be identified manually.

set Holiday= '01/05/2018','22/05/2018','24/05/5018','19/5/2018';

Period:

NoConcatenate

start, end

01/05/2018, 28/05/2018

];

Data:

NoConcatenate

LOAD Distinct *,NetWorkDays(start, end,\$(Holiday)) as networkday Resident Period;

DROP Tables Period;

Result:

From the above script, networkday is showing 18 instead of 24 (28days - 4 holidays)

Can anybody help me how to get the result for networkdays = 24?

1 Solution

Accepted Solutions
Creator III

Set Holiday= "'01/05/2018','22/05/2018','24/05/2018','19/5/2018'";

Period:

NoConcatenate

ID, start, end

A1123, 01/05/2018, 28/05/2018

A1145, 08/05/2018, 20/05/2018

B3266, 19/05/2018, 23/05/2018

];

Join (Period)

Holiday

'01/05/2018'

'22/05/2018'

'24/05/2018'

'19/5/2018'

];

Period1:

Load *, if (Holiday >=start and Holiday<=end ,1 , 0) as Holiday_Count

resident Period;

drop table Period;

Period12:

NoConcatenate

Load ID, start, end,sum(Holiday_Count) as Holiday_Count

resident Period1

group by ID, start, end;

drop table Period1;

Period_New:

Load *,Interval(end - start, 'D')+1 - Holiday_Count as WorkingDays resident Period12;

drop table Period12;

9 Replies
Creator III

Hi Haniff,

The networkdays function returns the number of working days (Monday-Friday).

You can use the Interval function, it will shows the days between 2 dates.

Interval(end - start, 'D')

then minus  the holidays

Interval(end - start, 'D') - \$(Holiday)

Thanks,

Sibin

Creator

I already used your suggested formula but the result return as below. May be you have other solution for me to get networkday result as 24.

Creator III

set Holiday= '01/05/2018','22/05/2018','24/05/5018','19/5/2018';

Let Holiday_Count =if(len(Holiday)=0,0,

if(SubStringCount(Holiday,',')=0,1,

SubStringCount(Holiday,',')+1

));

Period:

NoConcatenate

start, end

01/05/2018, 28/05/2018

];

Period_New:

Load *,Interval(end - start, 'D')+1 -\$(Holiday_Count) as test resident Period;

drop table Period;

Creator

Thx for the good trick Sibin. It very useful for my other project.

Actually for the purpose of discussion, I got a different date range for Start and end periods. Is there any solution for below script?

set Holiday= '01/05/2018','22/05/2018','24/05/5018','19/5/2018';

Period:

NoConcatenate

ID, start, end

A1123, 01/05/2018, 28/05/2018

A1145, 08/05/2018, 20/05/2018

B3266, 19/05/2018, 23/05/2018

];

Data:

NoConcatenate

LOAD Distinct *,NetWorkDays(start, end,\$(Holiday)) as networkday Resident Period;

DROP Tables Period;

Creator III

Set Holiday= "'01/05/2018','22/05/2018','24/05/2018','19/5/2018'";

Period:

NoConcatenate

ID, start, end

A1123, 01/05/2018, 28/05/2018

A1145, 08/05/2018, 20/05/2018

B3266, 19/05/2018, 23/05/2018

];

Join (Period)

Holiday

'01/05/2018'

'22/05/2018'

'24/05/2018'

'19/5/2018'

];

Period1:

Load *, if (Holiday >=start and Holiday<=end ,1 , 0) as Holiday_Count

resident Period;

drop table Period;

Period12:

NoConcatenate

Load ID, start, end,sum(Holiday_Count) as Holiday_Count

resident Period1

group by ID, start, end;

drop table Period1;

Period_New:

Load *,Interval(end - start, 'D')+1 - Holiday_Count as WorkingDays resident Period12;

drop table Period12;

Creator III

Hi,

Refer this

Find Net Working Days

Thanks,

Pooja

Creator

Thx Pooja,

Actually I am quite new with QV. I already try that trick before I post this discussion. I can't understand / didn't know how to adopt the script into my actual script.

Creator

Thx Sibin

Creator

I know this is old, but I have just done this with the networkdays formula and it seemed an easier method

set vHolidays= '01/05/2019','22/05/2019','24/05/5019','19/5/2019';

([End Date]-[Start Date]-(NetWorkDays([Start Date],[End Date])-NetWorkDays([Start Date],[End Date],\$(vHolidays)))) as [Start To End Days],//Remove holidays but leave weekends

Tags