Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to pull the 'Dates Off' field into the formula below instead of manually entering them one by one.
NetWorkDays([StartDate], [EndDate]) as "ExcludeWeekendHoliday"
Here is my code, can you help?
[Exclude Days]:
LOAD * INLINE [
'Dates Off',
'1/1/2018',
'1/15/2018',
'2/19/2018',
'3/30/2018',
'4/27/2018',
'5/28/2018',
'6/29/2018',
'7/5/2018',
'7/4/2018',
'8/14/2018',
'9/3/2018',
'10/31/2018',
'11/22/2018',
'11/23/2018',
'12/24/2018',
'12/25/2018',
]
;
tblCalculation:
Load
StartDate,
EndDate,
NetWorkDays([StartDate], [EndDate]) as "ExcludeWeekendHoliday"
Resident Supply ;
Drop Table Supply ;
Are you asking how to take the [Dates Off] into account in the NetWorkDays() calculation. Then do something like (assuming D/M/YYYY is your default date format):
[Exclude Days]: LOAD Concat(Chr(39) & [Dates Off] & Chr(39), ',') as Holidays ; LOAD * INLINE [ Dates Off, 1/1/2018, 1/15/2018 ... 12/24/2018, 12/25/2018, ] ; Let vHolidays = Peek('Holidays'); DROP Table [Exclude Days]; NetWorkDays(StartDate, EndDate, $(vHolidays)) as ...
vHolidays should contain a comma separated list of quotes dates. NetWorkDays() will now consider the dates in vHolidays as non-working, in addition to the weekends.
Are you asking how to take the [Dates Off] into account in the NetWorkDays() calculation. Then do something like (assuming D/M/YYYY is your default date format):
[Exclude Days]: LOAD Concat(Chr(39) & [Dates Off] & Chr(39), ',') as Holidays ; LOAD * INLINE [ Dates Off, 1/1/2018, 1/15/2018 ... 12/24/2018, 12/25/2018, ] ; Let vHolidays = Peek('Holidays'); DROP Table [Exclude Days]; NetWorkDays(StartDate, EndDate, $(vHolidays)) as ...
vHolidays should contain a comma separated list of quotes dates. NetWorkDays() will now consider the dates in vHolidays as non-working, in addition to the weekends.