topic Problem with NetWorkDays() by including holidays in QlikView

Problem with NetWorkDays() by including holidays

newqlikie — Mon, 27 Dec 2010 16:20:47 GMT

Hi everybody,

I try to get the networkdays for the last year, beginning with the latest loaded date. If I do not include the holidays, the function is working well.

If I include the holidays, the days will not change.

Here is my function, which is added to a textbox:

=NetWorkDays((makedate(year(max(Datum))-1, Month(max(Datum)), Day(Max(Datum)))), max(Datum), Feiertage)

The "Feiertage" is loaded in the script as follows:

tblFeiertage:
crosstable(FTJahr,FeierTG,1)
LOAD Beschreibung,
[2010],
[2011],
[2012],
[2013],
[2014],
[2015],
[2016],
[2017],
[2018],
[2019],
[2020],
[2021],
[2022],
[2023],
[2024],
[2025],
[2026],
[2027],
[2028],
[2029],
[2030]
FROM
F:\QlikView\Anwenderdateien\FeiertageDE.xls
(biff, embedded labels, table is Tabelle1$);

load concat(date(FeierTG), ',') as Feiertage
resident tblFeiertage;

What do I do wrong? Can anybody help me in this problem?

I really appreciate your help.

Regards,

NewQlikie

Problem with NetWorkDays() by including holidays

Miguel_Angel_Baeyens — Mon, 27 Dec 2010 17:00:56 GMT

Hello,

I think that you only need to pass the values in the field to a variable than can be expanded to an actual string, that is what NetWorkDays() is expecting. So try

load concat(date(FeierTG), chr(39) & ',' & chr(39)) as Feiertageresident tblFeiertage; LET vFeiertage = chr(39) & Peek('Feiertage', 0) & chr(39); // chr(39) is the single quote

Check that the dates are returned as strings 'DD/MM/YYYY' and that the function works fine.

Hope this helps

EDIT: Using LET instead of SET works.

Problem with NetWorkDays() by including holidays

johnw — Mon, 27 Dec 2010 22:25:49 GMT

In case it's useful, attached is an example of setting up custom firstworkdate and networkdays functions with a holiday list from a data source. Below is the relevant portion of the script:

Holidays:
LOAD * INLINE [
Holiday
3/15/2010
3/10/2010
2/15/2010
1/1/2010
];

AllHolidays:
LOAD concat(num(Holiday),',') as AllHolidays RESIDENT Holidays;
LET customfirstworkdate = 'firstworkdate($1,$2,' & peek('AllHolidays') & ')';
LET customnetworkdays = 'networkdays($1,$2,' & peek('AllHolidays') & ')';
DROP TABLE AllHolidays;

AW:Re: Problem with NetWorkDays() by including holidays

newqlikie — Tue, 28 Dec 2010 09:47:31 GMT

Hi John,

thank you very much. It worked.

Regards,

AW:Re: Problem with NetWorkDays() by including holidays

newqlikie — Tue, 28 Dec 2010 09:49:29 GMT

Hi Miguel,

thanks a lot for your answer, but I could not solve the problem with your suggestion.

I finally fixed it with John's suggestion.

Regards