Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Exclude holidays using Networkdays() in script

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Master
Master

Re: Exclude holidays using Networkdays() in script

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

View solution in original post

3 Replies
Highlighted
Master
Master

Re: Exclude holidays using Networkdays() in script

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

View solution in original post

Highlighted
Contributor II
Contributor II

Re: Exclude holidays using Networkdays() in script

Hi Sokkom,

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

Regards,

Upendra

Highlighted
Contributor II
Contributor II

Re: Exclude holidays using Networkdays() in script

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 DateEnd DateActualDays
2017.09.042017.09.041
2017.09.042017.09.051

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

  

Start DateEnd DateActualDays
2017.09.042017.09.041
2017.09.042017.09.05

2

Any help is appreciated!