Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rido1421
Creator III
Creator 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
sunny_talwar

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

View solution in original post

8 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rido1421
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

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

How about..

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

rido1421
Creator III
Creator III
Author

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

sunny_talwar

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
Creator III
Creator III
Author

Legend!!!

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