topic Re: QLIKview networkdays ignore weekend in QlikView

QLIKview networkdays ignore weekend

mohdhaniff — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: QLIKview networkdays ignore weekend

sibin_jacob — Tue, 31 Jul 2018 03:42:28 GMT

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

Re: QLIKview networkdays ignore weekend

mohdhaniff — Tue, 31 Jul 2018 03:59:06 GMT

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.

Re: QLIKview networkdays ignore weekend

sibin_jacob — Tue, 31 Jul 2018 04:18:02 GMT

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;

Re: QLIKview networkdays ignore weekend

mohdhaniff — Tue, 31 Jul 2018 04:26:30 GMT

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;

Re: QLIKview networkdays ignore weekend

sibin_jacob — Tue, 31 Jul 2018 05:19:28 GMT

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;

Re: QLIKview networkdays ignore weekend

pooja_prabhu_n — Tue, 31 Jul 2018 05:50:04 GMT

Hi,

Refer this

Find Net Working Days

Thanks,

Pooja

Re: QLIKview networkdays ignore weekend

mohdhaniff — Tue, 31 Jul 2018 07:02:10 GMT

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.

Re: QLIKview networkdays ignore weekend

mohdhaniff — Tue, 31 Jul 2018 07:47:27 GMT

Thx Sibin

Re: QLIKview networkdays ignore weekend

chriswain2 — Wed, 08 Jan 2020 11:51:47 GMT

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