Count Number of Holidays From Two Date Fields

[VanMild11]

Hello, I need help to find resolutions from this issue..

I have a table consists of transaction-date, input-date, and interval

I want to count how many days from the transaction to input date excluding holidays, so from interval 4 days, I expected its only 1 day. 
I've tried using NetworkDays() function, but apparently I unable to get the result I need 
I tried to write like this: 

NetworkDays(Transaction_date,Input_Date,Holidays)

Also I tried to count using set Expression, but apparently it will not work per line basis:

=count({< [DATEPERIOD] = {">=$(=Date('transaction_date','yyyy-MM-dd'))<=$(=Date('input_date,'yyyy-MM-dd'))"}>}

holiday)

Any Idea ?

Thank you so much

[brunobertels · Accepted solution]

Hi 

Networkdays function use the default format date DD/MM/YYYY 

So first chek if you specify it in the script : 

SET DateFormat='YYYY-MM-DD';

then the third argument for holidays date expect this list format : '2023-01-23','2023-01-22', .... 

So you need to adapt your formula like this 

NetworkDays(Transaction_date,Input_Date,concat(chr(39)& Holidays& chr(39),','))

Or create a variable 

tmp_Hollidays:

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

from ... 

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

drop table tmp_Hollidays;

then use this function 

NetworkDays(Transaction_date,Input_Date,$(Holidays))

Hope it helps 

[VanMild11]

Hi,

it worked out.. Contained the holiday list through concat and using vHolidays as holiday in networkdays.