Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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
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.
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;
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;
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;
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.
Thx Sibin
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