Introduction
Subscribe: bit.ly/3i3WN4p How do you calculate the number of working days between two dates in this example I have a parameter showing start date and a parameter for end date there are 47 working days between the 26th of October and the 31st of December let’s take a look at how to do this in tableau foreign
Start by creating parameters so I’m going to create a parameter for start date
Make it a date and let’s just do 12 1. 2022. Click ok and duplicate that and change it to end date
And make it 12 31.. Okay. Now to make this count work you may need to make sure you have a date a record for every single date within that range I have a date table that looks just like this you have one record for every date I’m gonna have to join that into my data in tableau says double click on my data go into my data table I’m gonna add the dates table open that we’re going to join it in on date so I have order date equals date I’m gonna do a full outer because I want there to be a date a record for every date in this range.
Let’s go over to our sheet. Start by dragging this new date field from your date table onto Rose and change it to exact date and let’s make this discrete. To add my start and end date to The View
I’m going to start writing a calculation. Create a calculated field and let’s start simple called count of days
It’s gonna be an if statement if date make sure you use the date from your date table is greater than or equal to start date and date is less than or equal to end date.
I’m gonna do a lot of and clauses here and it’s not a weekend let’s take a copy of date by holding Ctrl I’m dragging it to the right I want to change this to weekday so we can see what we’re doing
So I want to test if weekday of date and just drag that in is not equal to 1 which is Sunday and
Weekday is not equal to 7 which is Saturday let’s just add date. Here so if it matches all of this then we’re going to Output date click apply and put count of days into the view. So we can see what we’re doing
Scroll down to the bottom here. And you can see that all these are populated except for weekends
Holidays are a bit more tricky you have to hard code them
Change this then to an if it matches all of this they’re going to test if date is in one of the holidays so let’s just start taping one holiday so uh December 25th 2012. So now we’re testing if date is in one of the holidays you can add commas and add more dates then no else date and we can remove this then clause for now. And then either then here click apply.
Okay and you can see everything is null if it doesn’t count if it’s in a holiday or if it’s on a weekend it shows up as null let’s go back into our account field. And change this I want to adjust it to count distinct of all of these values it’s going to change my aggregation so it’s going to break click OK anytime you see a red pill remove it and drag it back in.
Let’s put it on label now we have a 1 for each day that we want to count and I don’t need date or weekday anymore and you can see the count of days is 22. And I could change this to adjust the count of days hope this helps
Content
How do you calculate the number of working days between two dates? In this example? I have a parameter showing start date and a parameter for end date.
There are 47 working days between the 26th of October and the 31st of December.
Let's take a look at how to do this in tableau, foreign start by creating parameters so I'm going to create a parameter for start date, make it a date and let's just do 12 1., 2022 click, ok and duplicate that and change it to end date and make it 12 31.
Okay, now to make this count work you may need to make sure you have a date, a record for every single date within that range.
I have a date table that looks just like this.
You have one record for every date.
I'm gonna have to join that into my data in tableau, says: double click on my data.
Go into my data table, I'm gonna.
Add the dates table open that we're going to join it in on date, so I have order date equals date.
I'm gonna do a full outer because I want there to be a date.
A record for every date in this range: let's go over to our sheet start by dragging this new date field from your date table onto Rose and change it to exact date, and let's make this discrete to add my start and end date to The View I'm going to start writing a calculation create a calculated field and, let's start simple, called count of days.
It's gonna be an if statement if date make sure you use the date from your date.
Table is greater than or equal to start date and date is less than or equal to end date.
I'm gonna do a lot of and clauses here and it's not a weekend.
Let's take a copy of date by holding Ctrl I'm, dragging it to the right.
I want to change this to weekday.
So we can see what we're doing so.
I want to test, if weekday of date and just drag that in is not equal to 1, which is Sunday and weekday is not equal to 7, which is Saturday, let's just add date here.
So if it matches all of this, then we're going to Output date.
Click apply and put count of days into the view, so we can see what we're doing scroll down to the bottom here and you can see that all these are populated, except for weekends holidays, are a bit more tricky.
You have to hard code them change.
This then to an if it matches all of this they're going to test if date is in one of the holidays.
So let's just start taping one holiday, so uh December, 25th 2012.
So now we're testing.
If date is in one of the holidays.
You can add commas and add more dates, then no else date and we can remove this, then clause for now and then either then here click apply.
Okay and you can see everything is null if it doesn't count if it's in a holiday or if it's on a weekend, it shows up as null.
Let's go back into our account field and change.
This I want to adjust it to count distinct of all of these values.
It's going to change my aggregation, so it's going to break click.
Ok anytime, you see a red pill, remove it and drag it back in.
Let's put it on label now we have a 1 for each day that we want to count and I don't need date or weekday anymore, and you can see.
The count of days is 22.
and I could change this to adjust the count of days hope this helps.
