Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
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.

Sample Load Script:


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

Period:

NoConcatenate

load distinct * Inline [

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?

hanif.JPG


1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Please try this one.

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

Period:

NoConcatenate

load distinct * Inline [

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)

Load * inline [

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;

View solution in original post

9 Replies
Highlighted
Creator III
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

Highlighted
Creator
Creator

thx Sibin for your reply.

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.

hanif.JPG

Highlighted
Creator III
Creator III

Please use the below script.

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

load distinct * Inline [

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;

Highlighted
Creator
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

load distinct * Inline [

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;

Highlighted
Creator III
Creator III

Please try this one.

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

Period:

NoConcatenate

load distinct * Inline [

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)

Load * inline [

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;

View solution in original post

Highlighted
Creator III
Creator III

Hi,

Refer this

Find Net Working Days

Thanks,

Pooja

Highlighted
Creator
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.

Highlighted
Creator
Creator

Thx Sibin

Highlighted
Creator
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