7 Replies Latest reply on May 26, 2015 9:40 AM by Steve Mayer

# Calculating Networkdays with Tableau

Hi,

I want to calculate/create a new measure "net workdays" for my time sheet reports from my date dimension. With excel i can just use the Net-workdays function: Net-workdays(Start Date, End Date) and i get a count. The number i get goes into 2 different calculation [(Monthly available hours (8 hours x net-workdays) and workday (total time entered divided by net-workdays)]. I want to give the users the ability to pick whatever date periods they want and these calculations adjust accordingly.

• ###### 1. Re: Calculating Networkdays with Tableau

Given that the same function doesn't exist, you'll need to look into alternatives.

One such way I can think of is to connect to an Excel file or other data source which contains a list of dates along with the classification of whether or not it's a workday. This would allow you to classify holidays as well

 Date IsWorkday 4/1/2014 TRUE 4/2/2014 TRUE 4/3/2014 TRUE 4/4/2014 TRUE 4/5/2014 FALSE 4/6/2014 FALSE 4/7/2014 TRUE 4/8/2014 TRUE 4/9/2014 TRUE 4/10/2014 TRUE 4/11/2014 TRUE 4/12/2014 FALSE 4/13/2014 FALSE 4/14/2014 TRUE 4/15/2014 TRUE 4/16/2014 TRUE

You could join this to your existing timesheet date dimension and do a count on the IsWorkday field.

1 of 1 people found this helpful
• ###### 2. Re: Calculating Networkdays with Tableau

Hi Paul.

I created a calculated field that computes weekdays from two Date dimensions. The formula adds the (number of full weeks * 5) plus the number of weekdays in the remainder (the part that isn't counted in the number of full weeks).

Check out the attached workbook, which displays weekdays for the month of April starting from the first 7 days of the month.

1 of 1 people found this helpful
• ###### 3. Re: Calculating Networkdays with Tableau

Hi Tom,

I tried that method, and i am getting the count of all rows (i think) that has dates in them. Here is a sample of my workbook and the excel sheet with my data.

2 of 2 people found this helpful
• ###### 4. Re: Calculating Networkdays with Tableau

Ahh I see how you are approaching this. Try the attached. I created a calculated field that only returns dates that are weekdays, and then added it as a measure with COUNTD. The tab "Number of Weekdays FIXED" shows an example with a total count of 21 weekdays.

This method doesn't require the external Excel file, since the calculation is just based off of the date. If you still want to take advantage of the Excel file (to account for holidays, etc.), simply change the Week Day Date calculation to:

IF [Is Bus Day] = "YES" THEN

[Date]

ELSE

NULL

END

Hopefully that helps.

1 of 1 people found this helpful
• ###### 5. Re: Calculating Networkdays with Tableau

Steve,

Thank you Steve! This is exactly what i needed.

• ###### 6. Re: Calculating Networkdays with Tableau

Hello Steve,

I would like to see this example but I just have Tableau Public, can you upload it to Tableau Public for me to see it?

• ###### 7. Re: Calculating Networkdays with Tableau

Patricia -

Here is a link to the same workbook on Tableau public:

https://public.tableau.com/views/SampleforMarch/NumberofWeekdaysFIXED?:embed=y&:showTabs=y&:display_count=yes

-Steve

1 of 1 people found this helpful