topic Re: Using networkdays and public holidays in qlikview in QlikView

Using networkdays and public holidays in qlikview

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using networkdays and public holidays in qlikview

varshavig12 — Thu, 04 Aug 2016 11:45:48 GMT

Something like this:

If yes,

Use ApplyMap()

If not post a sample output, you want only in script ?

Re: Using networkdays and public holidays in qlikview

Anonymous — Mon, 08 Aug 2016 10:08:03 GMT

It does not work in my case because there might be 2 public holidays for one repair

Re: Using networkdays and public holidays in qlikview

beck_bakytbek — Mon, 08 Aug 2016 11:22:04 GMT

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

Re: Using networkdays and public holidays in qlikview

trdandamudi — Mon, 08 Aug 2016 12:54:30 GMT

Please look at the below link which was Answered in the community. Hope this helps...

Exclude holidays using Networkdays() in script

Re: Using networkdays and public holidays in qlikview

Anonymous — Tue, 09 Aug 2016 05:53:08 GMT

Thanks. will try it. Hope it works since I have a very large volume of data

Re: Using networkdays and public holidays in qlikview

Anonymous — Tue, 09 Aug 2016 10:04:10 GMT

Hi.

It still does not work I have attached my sample application !

Kindly help

Thanks

Hasnaa !

Re: Using networkdays and public holidays in qlikview

trdandamudi — Tue, 09 Aug 2016 18:20:27 GMT

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.

Re: Using networkdays and public holidays in qlikview

Anonymous — Fri, 12 Aug 2016 10:08:45 GMT

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

Re: Using networkdays and public holidays in qlikview

trdandamudi — Fri, 12 Aug 2016 16:19:12 GMT

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();