topic Re: NetworkDays in QlikView

NetworkDays

rido1421 — Tue, 20 Sep 2016 08:28:59 GMT

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,

Re: NetworkDays

Anil_Babu_Samineni — Tue, 20 Sep 2016 08:32:31 GMT

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

Re: NetworkDays

rido1421 — Tue, 20 Sep 2016 08:35:44 GMT

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;

Re: NetworkDays

Tue, 20 Sep 2016 09:34:12 GMT

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.

Re: NetworkDays

rido1421 — Tue, 20 Sep 2016 09:47:26 GMT

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

Re: NetworkDays

Tue, 20 Sep 2016 09:57:37 GMT

How about..

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

Re: NetworkDays

rido1421 — Tue, 20 Sep 2016 10:02:54 GMT

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

Re: NetworkDays

sunny_talwar — Tue, 20 Sep 2016 10:28:08 GMT

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;

Re: NetworkDays

rido1421 — Tue, 20 Sep 2016 10:49:11 GMT

Legend!!!

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