Reduce difference one to zero

[leobrand]

What is the best way to update this expression
NetWorkDays(StartDate, EndDate, $(vHolidays)) as ExcludeWeekendHoliday

to get this calculation

StartDate	EndDate	ExcludeWeekendHoliday
12/10/2018	12/10/2018	0

Instead of this calculation

StartDate	EndDate	ExcludeWeekendHoliday
12/10/2018	12/10/2018	1

I tried including -1 after $(vHolidays) but I don’t think that’s the best way to do it

[Exclude Days]:
LOAD Concat(Chr(39) & [Dates Off] & Chr(39), ',') as Holidays
;
LOAD *
INLINE [
Dates Off,
12/24/2018,
12/25/2018,
]
;

Let vHolidays = Peek('Holidays');
DROP Table [Exclude Days];

tblCalculation:
Load
StartDate,
EndDate,
NetWorkDays(StartDate, EndDate, $(vHolidays)) as ExcludeWeekendHoliday
Resident Supply ;
Drop Table Supply ;

[Anil_Babu_Samineni]

What are you trying in this variable? "vHolidays"

[Vegar · Accepted solution]

The NetWorkDays() includes both the start and end date when calculating.

To get a expression smarter than just subtracting a single day you will need to decide if you want to exclude the start or end day from your interval. When you done that I see two possible methods for solving your problem. Feel free to check them out.

1, reducing your interval. Adding one day to the start day or subtracting one day from the end day will reduce your number of days correctly. I'm not sure what will happen if you calculate the single day example NetWorkDays(today(), today()-1).

2. Adding the start day or end day of your interval as an custom holiday in the third parameter in the NetWorkDays().