Networkdays function issue

[rajarshi_guin]

Hi All,

I am facing a problem in using the networkdays function. I am using the below formula.

Networkdays([Target Date],$(vEndDate),$(vHolidayDate))

The Target Date is picked from a table column, $(vEndDate) contains the date selected in filter and $(vHolidayDate) contains the dates for the holiday period.

So, for example, if I want to calculate the workdays between 09/01/2017 and 09/30/2017 where the period 09/20/2017-09/22/2017, the values I am passing to the three arguments is as below.

Target Date- 09/01/2017

$(vEndDate)- 09/30/2017

$(vHolidayDate)- '09/20/2017','09/22/2017'

Now the problem is the output is same both with and without the holiday dates. So, the formulas Networkdays([Target Date],$(vEndDate)) and Networkdays([Target Date],$(vEndDate),$(vHolidayDate)) both return the same output.

I am assuming that this error is occurring as I am not storing the values in the correct format in the variable $(vHolidayDate). So will this variable contain the data in the format shown above with the quotes or do I need to store the values in this variable in a different format.

Also all the dates have been converted to MM/DD/YYYY using Date and Floor function.

Can anyone please let me know how to resolve this.

Thanks,

Rajarshi

[agigliotti]

I just tried using the below expression:

=Networkdays(date#('09/01/2017'),date#('09/30/2017'),date#('09/20/2017'),date#('09/22/2017'))

and it works as expected.

[rajarshi_guin]

Hi Andrea,

Thanks for the response. If I write the formula in this way it is working. But my problem is that the holiday dates will not be constant. Here I have mentioned only one holiday date period 09/20/2017-09/22/2017. But this period is not fixed. Even there can be more than 1 holiday period. So I need to store these dates in a variable. So, my question is how to implement the formula where more than holiday periods are stored in a variable.

[agigliotti]

lt's try using below expression:

=Networkdays( date#('09/01/2017'),date#('09/30/2017'), $(=(vHoliday)) )

where vHoliday variable is date#('09/20/2017'),date#('09/22/2017')

[rajarshi_guin]

Hi Andrea,

This also did not work. I am storing the values in the variable using the below script.

ListTable_DT_DIM:

LOAD Date(Floor(MO_DAY_YR_DT),'MM/DD/YYYY') as MO_DAY_YR_DATE

From lib://Transform\DT_DIM.qvd(qvd);

Holiday_Date_List:

LOAD concat(date#(chr(39) & MO_DAY_YR_DATE & chr(39)),',') as List

Resident ListTable_DT_DIM;

LET vHolidayDate=List;

Is this the correct way to store the dates in the variable?

[agigliotti]

could you provide a sample qvf to help you quickly?