Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rido1421
Contributor III

NetworkDays

Hi There

I have an issue with the networkdays function in the way I am using it.

When Subtracting  [Posted/Issue]  from Filedate it returns the difference which is what I need as the Age.

In the Table below for Post issue date =2016/08/30 the age is 2 days yet the networkdays is 3.

I need the networkdays to work accordingly to my age.

Also I understand that networkdays in qlikview will not show negatives as a standard but in this case I need it to work accordingly with the age.

I have attached the data and the model.

Filedate

Posted/Issue

Age

Networkdays

NetworkdaysWithoutPublicholidays

2016/09/01

2016/07/30

33

24

22

2016/09/01

2016/08/02

30

23

21

2016/09/01

2016/08/05

27

20

19

2016/09/01

2016/08/18

14

11

11

2016/09/01

2016/08/22

10

9

9

2016/09/01

2016/08/23

9

8

8

2016/09/01

2016/08/24

8

7

7

2016/09/01

2016/08/25

7

6

6

2016/09/01

2016/08/26

6

5

5

2016/09/01

2016/08/27

5

4

4

2016/09/01

2016/08/29

3

4

4

2016/09/01

2016/08/30

2

3

3

2016/09/01

2016/08/31

1

2

2

2016/09/01

2016/09/01

0

1

1

2016/09/01

2016/09/02

-1

0

0

2016/09/01

2016/09/03

-2

0

0

2016/09/01

2016/09/05

-4

0

0

2016/09/01

2016/09/06

-5

0

0

2016/09/01

2016/09/07

-6

0

0

Regards,

1 Solution

Accepted Solutions

Re: NetworkDays

May be try this:

Dates:

Load *,

  Filedate-[Posted/Issue] as Age,

  If([Posted/Issue] < Filedate, NetWorkDays([Posted/Issue], Filedate)-1, -NetWorkDays(Filedate, [Posted/Issue])+1) as Networkdays,

  If([Posted/Issue] < Filedate, NetWorkDays([Posted/Issue], Filedate,$(vPublicHolidays))-1, -NetWorkDays(Filedate, [Posted/Issue],$(vPublicHolidays))+1) as NetworkdaysWithoutPublicholidays

Resident Table1;

DROP Table Table1;


Capture.PNG

8 Replies

Re: NetworkDays

I don't have license on my machine

Can you please share the script what you are tried for she, networking days, networking without public days so that might we help you

Life is so rich, and we need to respect to the life !!!
rido1421
Contributor III

Re: NetworkDays

Thanks Anil.

[Public Holidays]:

LOAD Date(EventDate,'YYYY/MM/DD') as PublicHoildayDate, //[Pay Date],
   // Date(Date,'MM/DD') as [Pay Date link],
   [Public Holiday],
'Hol'
as Ind


FROM

(
ooxml, embedded labels, table is Sheet1);



PubHolidays:
Load Concat(Num(PublicHoildayDate),',') as PublicHolidays Resident [Public Holidays];
LET customfirstworkdate='firstworkdate($1,$2,'&Peek('PublicHolidays') & ')';
LET customnetworkdays  = 'networkdays($1,$2,' & peek('PublicHolidays') & ')';
LET vPublicHolidays  = peek('PublicHolidays');



Table1:
LOAD
[Posted/Issue],
'2016/09/01'
as Filedate
FROM

(
ooxml, embedded labels, table is export);


Dates:
Load *,
Filedate-[Posted/Issue] as Age,
NetWorkDays([Posted/Issue],Filedate) as Networkdays,
NetWorkDays([Posted/Issue],Filedate,$(vPublicHolidays)) as NetworkdaysWithoutPublicholidays

Resident Table1;
DROP Table Table1;

Not applicable

Re: NetworkDays

I guess there are 2 difference between Age = Date1 - Date2 and networkdays between Date1 and Date2.

1. Age doesnt count Date2 (the start date), and so we are seeing lots of 1 day difference between Age and networkdays. If want to align Age and networkdays, may simply -1.

2. Networkdays doesnt provide negative values. If file date before post/issue date, which value to be returned? may use If function to calculate separately.

rido1421
Contributor III

Re: NetworkDays

Hi Youyun

I have applied your 1st suggestion and am testing if it will be dynamic.

Your 2nd suggestion will give a split as in what is lower than equal to the post/issue date however I actually require the amount of days.

Is there perhaps an alternative way to calculate the networkingdays so I could try and exclude the (Start date) of Date2 manually and also get the negative values in working days.

Thanks

Not applicable

Re: NetworkDays

How about..

if(NetWorkDays([Posted/Issue],Filedate) < 0,  - NetWorkDays([Filedate, [Posted/Issue]) + 1, NetWorkDays([Posted/Issue],Filedate) - 1)

rido1421
Contributor III

Re: NetworkDays

I dont think this would work because Network days will never be less than 0...

Re: NetworkDays

May be try this:

Dates:

Load *,

  Filedate-[Posted/Issue] as Age,

  If([Posted/Issue] < Filedate, NetWorkDays([Posted/Issue], Filedate)-1, -NetWorkDays(Filedate, [Posted/Issue])+1) as Networkdays,

  If([Posted/Issue] < Filedate, NetWorkDays([Posted/Issue], Filedate,$(vPublicHolidays))-1, -NetWorkDays(Filedate, [Posted/Issue],$(vPublicHolidays))+1) as NetworkdaysWithoutPublicholidays

Resident Table1;

DROP Table Table1;


Capture.PNG

rido1421
Contributor III

Re: NetworkDays

Legend!!!

Seems to work well. I will test further. Thank You