Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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
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
(
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
(
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;
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.
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
How about..
if(NetWorkDays([Posted/Issue],Filedate) < 0, - NetWorkDays([Filedate, [Posted/Issue]) + 1, NetWorkDays([Posted/Issue],Filedate) - 1)
I dont think this would work because Network days will never be less than 0...
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;
Legend!!!
Seems to work well. I will test further. Thank You