Networkdays Function Implementation In Tableau

Networkdays Function Implementation In Tableau:

Problem Description-

Many business users would like to count the number of weekdays, or business days, between two dates in Tableau reports then Networkdays () will be useful.

Challenges to User/Developer-

The above problem can be resolved with the help of Networkdays Function or Working days between two days.

Solution

Approach- Function Implementation In Tableau

It calculates the absolute date difference between the start and end date.

Technical Solution –

Part 1: Working Days in Full Weeks Spanned

This gives us the number of working days in full weeks spanned. First, it calculates the absolute date difference between the start and end date here:

(DATEDIFF (‘day’, [Day1], [Day2])

Then we trim off days in the first partial week of the time frame. Subtracting 7 minus the weekday number takes us up to the end of the first Sunday.

– (7-DATEPART (‘weekday’, [Day1]))

Then we trim off the days in the partial week at the end of the time frame to take us back to the end of the last full week.

– DATEPART (‘weekday’, [Day2])

Now we have a number that is a multiple of 7 and will reflect the number of days in the full weeks between the start and end date. We divide this by 7 and multiply it by 5 to get the number of working days in these weeks.

) / 7*5

Part 2: Working Days in the First Partial Week

By default, Tableau counts Saturday as the 7th day of the week, and we want to count backwards how many working days have led up to it from our start date [day1] in our first partial week. This means if we take away the weekday number from 7 we will get the number of days up to Saturday. To stop us from getting a count of 6 working days when looking at Sunday through to Saturday, the date part snippet is wrapped in a ‘min’ calculation with the number 5 so we get no more than 5 working days in a week.                                     

 + MIN (5, (7-(DATEPART (‘weekday’, [Day1]))))

Part 3: Working Days in the Final Week

As Tableau counts Sunday as day 1 in its weekday calculations, we need to subtract 1 from our weekday calculation on our final date [day2] for the final week working days. This makes the Sunday through to Saturday counting go from 0 to 6. Like before in the first partial week, we need to then wrap the weekday calculation in a ‘min’ formula with the number 5 so we get no more than 5 working days in a week.

+ MIN (5, (DATEPART (‘weekday’, [Day2])-1))

Part 4: Making the Calculation Exclusive of the Start Date

At the moment the calculation is inclusive of both the start and end dates. This means a project starting and ending on the same day has a working day count of 1 day. If we want to look at full working days so that something starting on Wednesday and finishing on Thursday counts as 1, and the same day project counts as 0 days then we need to subtract 1 from our calculation -1

Part 5: Final calculated field

 (DATEDIFF (‘day’, [Day1], [Day2]) –

(7-DATEPART (‘weekday’, [Day1])) –

DATEPART (‘weekday’, [Day2])

) / 7*5

+ MIN (5, (7-(DATEPART (‘weekday’, [Day1]))))

+ MIN (5, (DATEPART (‘weekday’, [Day2])-1))

-1

Advantages-

The Networkdays function returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holiday.

Conclusion-

When we are migrating from Qlik sense to Tableau above solution will be more effective, Networkdays () will be effective functionality in Qlik sense so it will be done easily via Working days between two days functionality in Tableau. Business users would like to count the number of weekdays, or business days, between two dates in reports then Networkdays () will be useful.