How to have a parameter ignore weekends in Tableau?

1
votes

I have a capacity dashboard in Tableau that is designed for the user to select a start date and the days required for a new project to add onto a stacked bar chart. Currently, I am getting the wrong calculations due to this piece of my IF formula

AND [Date] <= [Start Date] + ([Days Required])

as it is including weekends in that part. I don't have weekends in my data source, my views, and date is discrete. Is there a function, or way to get that particular part of the IF formula to ignore weekends?

Thank you for any help!

2
tableau-api
Can you include the entire calculation and some sample data. You might be looking for what @shmicah put below... or you could be looking to skip weekends and just count business days. It isn't totally clear. Thanks. – tagyoureit

2 Answers

2
votes

datename('weekday',TODAY()) can give you the string name of the day - keyword being 'weekday' versus just 'day'. You can check:

if datename('weekday',TODAY())='Saturday' OR datename('weekday',TODAY())='Sunday'
1
votes

Here is something I came up with..

Q.Add days to start date by and skip the weekends (in other words, consider only weekdays).

Solution: Determine the end_date by adding the days to start_date [End Date]:

DATEADD('day',[Days],[Start Date])

I've created 2 calculated fields for this. 1. Add_days - To add days to [days] field when a week comes between [Start Date] and [End Date]. [Add_days]

[Days]+DATEDIFF('week',[Start Date],[End Date])+DATEDIFF('week',[Start Date],[End Date])
  1. End_date_final - To get the final end date that excludes weekends and gives a new End Date. [End_date_final]
IF DATENAME('weekday',DATEADD('day',[Add_days],[Start Date]))='Saturday'
THEN
DATEADD('day',[Add_days]+2,[Start Date])
ELSEIF DATENAME('weekday',DATEADD('day',[Add_days],[Start Date]))='Sunday'
THEN
DATEADD('day',[Add_days]+1,[Start Date])
ELSE
DATEADD('day',[Add_days],[Start Date])
END

Now you can use the condition in your IF condition as

AND [Date] <= [End_date_final]

Let me know if more help is needed. Have a nice day :)