[Discussion] NetWorkDays function, holidays argument format

[#1]

Hello community, I have started this discussion in order to get a question solved that me and some other members found on another thread and I think that might be of general interest.

On my way on finding a optimal way of calculating the networkdays between two dates but without including the holidays for differetn countries (see thread: Re: Bank holidays for multiple countries) I have prepared the following script in order to test the function:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';


HOLIDAYS:
LOAD * INLINE [START_DATE, END_DATE, HOLIDAY_DAYS
'1/2/2016', '13/2/2016', '10/02/2016',
'1/2/2016', '13/2/2016', '11/02/2016',
'1/2/2016', '13/2/2016', '9/02/2016'];


LEFT JOIN(HOLIDAYS)
LOAD CONCAT(chr(39) & HOLIDAY_DAYS & chr(39), ',') AS HOLIDAYS
RESIDENT HOLIDAYS;


LEFT JOIN(HOLIDAYS)
LOAD NETWORKDAYS(START_DATE, END_DATE, HOLIDAYS) AS WITH_HOL,
     NETWORKDAYS(START_DATE, END_DATE) AS NO_HOL
RESIDENT HOLIDAYS;

The results obtained from running the script:

As you can see, the field WITH_HOL is not properly calculated. So if we look at the formula LOAD NETWORKDAYS(START_DATE, END_DATE, HOLIDAYS) AS WITH_HOL, the field 'HOLIDAYS' is not beeing understood by the function. So my question is, why? if you put the content of the field into a variable, is properly understood but you loose the 'dynamic' property of the field, I mean, the multiple possible values for each country or customer the field may have. Until now, the only way to make the parameter switch is to perform  a loop but for large amounts of data this is not efficient.
If someone can provide me an answer or a solution. Thank you all in advance.

D.

[jpenuliar]

Hi Diana,

Have you looked at below logic:

Match countries to get holidays for NetWorkDays formula

[#3]

Yeah, I have checked this and this is how I have implemented in my script already but please note that I have a very large amount of data and also I have to perform a double loop as I have not only to iterate through many countries but also trough service centers (and it's holidays) so the time that my script takes to reload is very large and it must be reloaded few times each day. I'm searching for a efficient way to implement the same, maybe a macro? My problem is I know nothing about VB...

Thanks for your response
D.

[#4]

Do you wanna try variables?

HOLIDAYS: 

LOAD * INLINE [START_DATE, END_DATE, HOLIDAY_DAYS 

'1/2/2016', '13/2/2016', '10/02/2016', 

'1/2/2016', '13/2/2016', '11/02/2016', 

'1/2/2016', '13/2/2016', '9/02/2016']; 

Temp:

LOAD CONCAT(chr(39) & HOLIDAY_DAYS & chr(39), ',') AS HOLIDAYS 

RESIDENT HOLIDAYS; 

let HOLIDAYSVAR = PEEK('HOLIDAYS'); 

LEFT JOIN(HOLIDAYS) 

LOAD NETWORKDAYS(START_DATE, END_DATE, $(HOLIDAYSVAR)) AS WITH_HOL, 

     NETWORKDAYS(START_DATE, END_DATE) AS NO_HOL 

RESIDENT HOLIDAYS; 

[Anonymous]

you can use a workaround

store your Holidays into a variable and use this variable

e.g.

LEFT JOIN(HOLIDAYS) 
LOAD CONCAT(chr(39) & HOLIDAY_DAYS & chr(39), ',') AS HOLIDAYS 
RESIDENT HOLIDAYS; 
//new:
let vholidays=peek('HOLIDAYS',0);

LEFT JOIN(HOLIDAYS) 
LOAD NETWORKDAYS(START_DATE, END_DATE, $(vholidays)) AS WITH_HOL, 
NETWORKDAYS(START_DATE, END_DATE) AS NO_HOL 
RESIDENT HOLIDAYS; 

[#6]

Thanks for your answer but is not what I'm searching for as I need the Holidays to be different for each country and variables (especially those with LET) don't change once evaluated (they have a fixed value) so the same holidays will be evaluated for all the countries.

D.

[jpenuliar]

You mentioned, each Country has holiday dates, in your table HOLIDAYS, I dont see any field showing a country.

As the others have already mentioned, you can come up with variables for each countries, this is practical only when you have a number of countries you can count with your fingers.

[#8]

In the main message I've linked a thread that I did asking for a way to perform holidays for multiple countries and there's data sample. I made this thread because I noticed, networkdays() function is not working properly when using a field with various holidays (field created by concat) and how could I make it work. Thanks for all your kind responses, I'll keep thinking on a suitable solution, if you have any other proposition ill be glad to try it!

[jpenuliar]

Can you post a sample data/file?

[#10]

Let's imagine that my data consists on a table like this:

CUSTOMER_DATA
IDCustomer
CustomerCountry
StartDate
EndDate

and I wish to add a new field, obtained in the following way:

ServiceTime = NetWorkDays(StartDate, EndDate)

In this new field I wish to obtain the bussiness days the service has been active BUT taking into account the holidays for each IDCustomer and each country. I mean, for every IDCustomer there will exist a different number of holidays between their StartDate and their EndDate. If I have and Excel file with all the bank holidays for each country, wich I can load into my script, like:

COUNTRY_ID	BANK_HOLIDAY_DATE
Poland	12/03/2016
Poland	13/03/2016
...	...

In reality is not so simple because StartDate and EndDate are calculated checking some other fields but this is an approximation. Thanks for your responses.