Exclude holidays using Networkdays() in script

[upendrarajeshir]

Hi All,

I tried to calculate actual worked days using NetWorkDays(). I have country wise list of start date and end dates and have country wise list of holidays. Each country has different holidays. Now I want to calculate Actual Worked Days country wise .

I have created one chart in the attached QVW file, NetWorkDays() executes perfectly when I create variable but I want to do it in script so that it will calculate Actual Worked Days even if when I do not have selection over the country.

I attached sample Qvw and excel file.

Thanks in advance !!!

Regards,

Upendra

[Sokkorn · Accepted solution]

Hi Upendra,

This quite hard script, but I'm sure it work

Country:
LOAD Concat(DISTINCT chr(39) & Country &chr(39),',') AS CountryName
FROM
Book1.xlsx
(ooxml, embedded labels, table is CountryWorkDates);


Let vCountryList = Peek('CountryName',0,'Country');
DROP Table Country;


For Each vCountry in $(vCountryList)
  [Holiday]:
  LOAD Concat(chr(39) & Holiday &chr(39),',') AS HD
  FROM Book1.xlsx (ooxml, embedded labels, table is CountryHolidays) Where Country='$(vCountry)';
  Let vHoliday = Peek('HD',0,'Holiday');
  DROP Table Holiday;

  CountryWorkDates:
  LOAD
  Country, 
      StartDate, 
    EndDate,
      NetWorkDays(StartDate,EndDate,$(vHoliday)) as ActualDays
  FROM
  Book1.xlsx
  (ooxml, embedded labels, table is CountryWorkDates) Where Country='$(vCountry)';
NEXT vCountry


Let vCountry = Null();
Let vCountryList = Null();
let vHoliday = Null();

See attached file

Regards,

Sokkorn

[upendrarajeshir]

Hi Sokkom,

Its tricky but works for me. Thanks a lot !!!

Regards,

Upendra

[Anonymous]

Thanks for posting this. It seems to be working for me as well but I do have another challenge.

Is it possible to set up a logic that if the End Date is a holiday (from the holiday list) then exclude that day from the holiday list? What I mean is the following:

Holiday: 5 Sep 2017

Using your script, Qlik Sense returns the following:

Start Date	End Date	ActualDays
2017.09.04	2017.09.04	1
2017.09.04	2017.09.05	1

Is it possible to return 2 days for the scenario when the End Date is on a Holiday like below:

Start Date	End Date	ActualDays
2017.09.04	2017.09.04	1
2017.09.04	2017.09.05	2

Any help is appreciated!