Using networkdays and public holidays in qlikview

[Anonymous]

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

[varshavig12]

Something like this:

If yes,

Use ApplyMap()

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

[Anonymous]

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

[beck_bakytbek]

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

[trdandamudi · Accepted solution]

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

Exclude holidays using Networkdays() in script

[Anonymous]

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

[Anonymous]

Hi.

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

Kindly help

Thanks

Hasnaa !

[trdandamudi]

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.

[Anonymous]

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

[trdandamudi]

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