topic Re: Using a variable in NetWorkDays function? in QlikView

Using a variable in NetWorkDays function?

cfunkhouser — Thu, 15 Dec 2016 20:42:39 GMT

I have a .qvw file in which I'm using the NetWorkDays function quite often, with a long list of holiday dates, like this:

NetWorkDays(HRS_DT,DateEntered,'2016-1-1','2016-1-18','2016-2-15','2016-5-30','2016-7-4','2016-9-5','2016-11-24','2016-11-25','2016-12-26','2017-1-2','2017-1-16','2017-2-20')

Because I'm using the function so many times, I'd prefer to use a Variable in place of the long list of dates, so that when I have to add dates, I only have to do it in one place. So far I've been unable to do this; it is possible, and if so, how?

Thanks,

Chris Funkhouser

Re: Using a variable in NetWorkDays function?

vishsaggi — Thu, 15 Dec 2016 21:50:30 GMT

Did you try this?

LET vHols = MID('2016-1-1'&'2016-1-18'&'2016-2-15'&'2016-5-30'&'2016-7-4'&'2016-9-5'&'2016-11-24'&'2016-11-25'&'2016-12-26'&'2017-1-2'&'2017-1-16'&'2017-2-20', 2) ;

= NetWorkDays(HRS_DT,DateEntered,vHols)

Re: Using a variable in NetWorkDays function?

joseduque — Thu, 15 Dec 2016 22:38:35 GMT

Hi,

If you have the holiday dates in a field or you can create an excel with the holidays and then do this:

tmp_Holidays:

LOAD Holidays

FROM

[..\..\Holidays.qvd]

(qvd);

Here you create a variable with all the dates:

tmp_Concat:

LOAD concat(chr(39)& Holidays& chr(39),',') as All_Holidays

RESIDENT tmp_Holidays;

Let vHolidays = fieldvalue('All_Holidays'),1);

dont forget to drop tmp tables

Drop tables tmp_Holidays,tmp_Concat;

Then you use vHolidays with NetworkDays like this:

NetWorkDays(Date_Begin,Date_End,$(vHolidays))

Hope this Helps,

Jose

Re: Using a variable in NetWorkDays function?

cfunkhouser — Fri, 16 Dec 2016 03:34:57 GMT

Thanks, Jose! That worked like a charm!

Chris