Thank you for your response, Diego! I found this solution, but it doesn't resolve the holiday issue. The issue is if there is a date within the date range being counted that is a holiday, Tableau won't discount it.
What's your data source?
The actual workbook data source will be SQ
If you're pulling in from SQL, then I'd do this in the database.
Assuming you have a table in your database called HolidayCalendar which contains a field to determine if the date is a work day or not;
Date IsWorkday 2014-01-01 0 2014-01-02 1 2014-01-03 1 2014-01-04 0 2014-01-05 0 2014-01-06 1 2014-01-07 1
You have a couple of options;
Create a SQL Function
Create a sql function which accepts two dates (start and end) and looks up those dates in your holiday table to return a total amount of work days between the two dates. I.e. SELECT SUM(IsWorkDay) FROM HolidayCalendar WHERE Date between @startdate and @enddate.
You would then call this function for every record in the table you're trying to calculate the work day duration for. i.e. SELECT Project, fnGetWorkingDays(StartDate,EndDate) as WorkingDays FROM ...
Join to the Holiday table
SELECT p.Project, p.StartDate, p.EndDate, SUM(c.IsWorkDay)
FROM Project p
LEFT JOIN HolidayCalendar c ON c.Date between p.StartDate and p.EndDate
p.Project, p.StartDate, p.EndDate,
Did you find a solution. I tried your calculation to exclude the weekends but in few cases I'm getting negative days. I have attached my sample workbook and if you let me were I'm going wrong please.
WorkingDaysCalculation.twbx 34.6 KB
I like this post and it helps however I don't have access to add a holiday table in the database. I have a custom SQL query and was able to add a calculation to the workbook to calculate business days see below but still trying to figure out how to exclude the holidays. Is there anything I can add within Tableau or my calculation that would help me exclude the holidays. Any advice would be helpful
DATEDIFF('week',[Receive Date],[Close Date])*5