
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using networkdays and public holidays in qlikview
Hello
I would like to calculate the number of days between two dates, excluding public holidays and weekends. Actually it is to calculate the number of days a repair centre repairs broken apparatus whereby we have a time_In and time_Out. the repair centre does not work during weekends and public holidays. Moreover, I have data from multiple countries whereby there public holidays differ.
I have data for repairs as below:
Country_Code | Repair_ID | Date_In | Date_Out | Turn_around_Time |
FRA001 | FRA1 | 21/07/2016 | 01/08/2016 | ??? |
FRA001 | FRA2 | 01/08/2016 | 08/08/2016 | ??? |
FRA001 | FRA3 | 05/08/2016 | 19/08/2016 | ??? |
FRA001 | FRA4 | 01/08/2016 | 31/08/2016 | ??? |
ROA001 | ROA1 | 21/07/2016 | 08/08/2016 | ??? |
ROA001 | ROA2 | 05/08/2016 | 16/08/2016 | ??? |
ROA001 | ROA3 | 05/08/2016 | 19/08/2016 | ??? |
ROA001 | ROA4 | 01/08/2016 | 02/09/2016 | ??? |
I have the list of public holidays:
Country_Code | Public_Holiday_Date |
FRA001 | 27/07/2016 |
FRA001 | 15/08/2016 |
FRA001 | 27/08/2016 |
ROA001 | 04/08/2016 |
ROA001 | 17/08/2016 |
ROA001 | 27/08/2016 |
I need to calculate the turn_around time in qlikview.
I have loaded the public holidays using :
LOAD
Country_Code,
concat(chr(39) & Public_Holiday_Date & chr(39),',') as HolidayDates ;
let v_HolidayDates = Peek('HolidayDates');
To get the number of days excluding public holiday, I use:
NetWorkDays (Date_In, Date_Out,$(v_HolidayDates) ).
However, it only works when putting the holidays in a variable. Thus for each country, I will need a different variable and then I will have a long list of IF condition to know which variable to use.
Is there a way to do this without using variable and peek.
Thank you
Regards
Hasnaa
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please look at the below link which was Answered in the community. Hope this helps...


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Something like this:
If yes,
Use ApplyMap()
If not post a sample output, you want only in script ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It does not work in my case because there might be 2 public holidays for one repair

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Hasnaa,
i am not sure, but try this, as Minimum you can find more Information to your issue on the base of my example
only Wokring Days:
let vStartDay = date('01/01/2010');
let vEndDate = date('31/12/2016');
for x = vStartDay to vEndDate
tempCalendar:
load $(x) as Date AutoGenerate(1);
next x
NoConcatenate
Working_Calendar:
Load Date, weekday(Date) as WeekDay, week(Date) as Week, Month(Date) as Month, Year(Date) as Year, floor(month(Date)/3)+1 as Qtr
resident tempCalendar
where WeekDay(Date) <> 'Sat' and WeekDay(Date) <> 'Sun';
drop table tempCalendar;
i hope that helps
beck

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please look at the below link which was Answered in the community. Hope this helps...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks. will try it. Hope it works since I have a very large volume of data

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi.
It still does not work I have attached my sample application !
Kindly help
Thanks
Hasnaa !

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, not able to get enough time to look at it... But Please see the attached file which is working now: Hope this helps. If you think you got your answer, please make sure to mark it accordingly and close the thread.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello
It is not calculating the networkdays correctly for all countries because one of the variable in the for loop is not initializing correctly in the loop. In the country list I have 'FRA001', 'ROA001'. when looping on the first country the variable vHoliday is correctly peeking the list of holidays. Upon the second loop for ROA001, the variable vHoliday is remaining null after peek thus for ROA001 calculations, the networkdays is not taking into consideration the holidays. Kindly help
Thanks
Hasnaa
Country:
LOAD Concat(DISTINCT chr(39) & Country_Code &chr(39),',') AS CountryName
RESIDENT Holidays;
Let vCountryList = Peek('CountryName',0,'Country_Code');
DROP Table Country;
For Each vCountry in $(vCountryList)
[Holiday]:
LOAD Concat(chr(39) & Public_Holiday_Date &chr(39),',') AS HD
RESIDENT Holidays Where Country_Code='$(vCountry)';
Let vHoliday = Peek('HD',0,'Public_Holiday_Date');
TAT:
LOAD
Country_Code,
Repair_ID,
date#(Date_In,'DD/MM/YYYY') AS Date_In,
date#(Date_Out,'DD/MM/YYYY') AS Date_Out,
NetWorkDays(date#(Date_In,'DD/MM/YYYY'),date#(Date_Out,'DD/MM/YYYY'),$(vHoliday)) as TAT_Calc
RESIDENT Returns_Data Where Country_Code='$(vCountry)';
NEXT vCountry

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please see the updated attached file...
Holidays:
LOAD * INLINE [
Country_Code, Public_Holiday_Date
FRA001, 27/07/2016
FRA001, 15/08/2016
FRA001, 27/08/2016
ROA001, 04/08/2016
ROA001, 17/08/2016
ROA001, 27/08/2016
];
Returns_Data:
LOAD * INLINE [
Country_Code, Repair_ID, Date_In, Date_Out
FRA001, FRA1, 21/07/2016, 01/08/2016
FRA001, FRA2, 01/08/2016, 08/08/2016
FRA001, FRA3, 05/08/2016, 19/08/2016
FRA001, FRA4, 01/08/2016, 31/08/2016
ROA001, ROA1, 21/07/2016, 08/08/2016
ROA001, ROA2, 05/08/2016, 16/08/2016
ROA001, ROA3, 05/08/2016, 19/08/2016
ROA001, ROA4, 01/08/2016, 02/09/2016
];
Country:
LOAD Concat(DISTINCT chr(39) & Country_Code &chr(39),',') AS CountryName
RESIDENT Holidays;
Let vCountryList = Peek('CountryName',0,'Country');
DROP Table Country;
For Each vCountry in $(vCountryList)
[Holiday]:
LOAD Concat(chr(39) & Public_Holiday_Date &chr(39),',') AS HD
RESIDENT Holidays Where Country_Code='$(vCountry)';
Let vHoliday = Peek('HD',0,'Holiday');
TAT:
LOAD
Country_Code,
Repair_ID,
date#(Date_In,'DD/MM/YYYY') AS Date_In,
date#(Date_Out,'DD/MM/YYYY') AS Date_Out,
NetWorkDays(date#(Date_In,'DD/MM/YYYY'),date#(Date_Out,'DD/MM/YYYY'),$(vHoliday)) as TAT_Calc
RESIDENT Returns_Data Where Country_Code='$(vCountry)';
NEXT vCountry
Drop tables Holidays, Returns_Data;
Let vCountry = Null();
Let vCountryList = Null();
let vHoliday = Null();
