Jim Dehner 

 Tableau Visionary and Ambassador

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

Post Links

Working with Business Days

 

Often you only want to use "Business Day" in your viz or move the weekend sales to Friday or Monday –

 

 

 

In the examples here I will use the US standard Sunday – Saturday week with a Monday – Friday "Business Week"  – 

 

1-Weekday Labels

 

Tableau has a couple of ways to identify and label dates by the day of the week – 

A straightforward way is to take advantage of the Custom Date option

 

Open the Date dimension – then Custom Date – then Weekdays from the dropdown

 

 
 

Alternately – you can use Datename in a calculated filed – the argument 'weekday' must be the literal as shown in quotes

 

DATENAME('weekday',[Order Date])

 

Or use Datepart to assign a number to the weekday – 1 for the first day of the week (Sunday in my examples) and 7 for the last day (Saturday)

 

DATEPART('weekday',[Order Date]) 

 

 

Sales by day of the week are easy to get – but how do you move the weekend sales to Friday – You can use an "IF … Then " type of conditional or a Case statement like this

 

CASE [Order Date (Weekdays)]

When 2 then "Monday"

When 3 then "Tuesday"

When 4 then "Wednesday"

When 5 then "Thursday"

Else "Friday-Sunday" End

 

 
 

2- Examples

 
The day of the week dimension can be used like any other dimension to categorize data – 
 
or in any type of calculation
 
Some users want to look at how their average sales vary by the day of the week
 
 
 
 – Or like this YoY by day of the week – (see  YoY Blog Post )
 

 

 
 
 

3-Holidays

 
Holidays, including those that are unique to a region or business culture, often need to be removed from the dataset – 
 
One way is to create a separate dataset of the date and the holiday name:
 
 
 
Left join the holiday file with your data set on the date field
 

 

and then use a Data Source filter to delete all the holidays from the data
 
 
 

4-Look back N business days

 
Some users want to find a value in the several days before the current day – 
It can be done easily with a datediff function when all 7 dates are included.  But to look back N days excluding weekends is more complex.  
 
The example below is broken into individual formulas that you can combine in your workbook – also it determines the number of calendar days needed   – the solution uses actual date values so dimensional filters can be applied without affecting the calculation 
 
First, the Lookback period in business days is set using a parameter (just a simple integer)
 
 
 
To simplify some of them I wanted to use Monday as the first day of the week and used the previous Datepart week formula in:
 
if [date part weekday]>=1 and [date part weekday]<=7 then
[date part weekday]-1 else 0 end
 
 
You may be tempted to use datepart('weekday',[date],'monday')  – unfortunately, datepart only supports weekdays for weeks that begin on the default – in my case Sunday
 
Next, we need the number of 5 day weeks back based on the value of the Business Day Back Parameter – the Min Number of Weeks is:
 
Int(([Number of day back]+1)/5)
 
 
but that is the number of whole weeks and it needs to be adjusted for the position of the date within the week – for that use a function called modulo – 
 
the function returns the remainder of the division of the argument (the parameter value +1) divided by  5  
 
([Number of day back]+1)%5
 
The total number of weekends then is
 
{ FIXED [Order Date]:(
if min([jd datepart start monday])=0 then Min([JD Number of weeks ] )
elseif min([jd datepart start monday])<int(Min([jd modulo day of week]))
then Min([JD Number of weeks ])+1 else Min([JD Number of weeks ] )end ) }
 
 
The formula used to add in the Saturday/Sunday weekend days to the Business Days Back parameter to determine the number of calendar days to use in the date diff calculation
 
int(min([Number of day back]))  + 2*(min([number of weekend days]))
 
finally, the calendar date for N Business days in the past can be determined as
 
{ FIXED [Order Date] :DATE(dateadd('day',-[JD number of dates back],Min([Order Date])))   }
 
For example, using December 2019 Superstore data looking back 7 business days would return :
 
 

Great but real business questions are usually about the growth from the over the period or the total sales looking back over all the dates.  To get that we need a start date and a parameter. 

First, determine and fix the calendar date based on the start date and the number of business days to look back

 
{ FIXED : min(if [Enter start date]=[Order Date] then [JD date of back dated] end )}
 
then the value on that date becomes:
 
IFNULL({ FIXED :sum(if [JD Fixed back date from param]= 
[Order Date] then [Sales] end)}  ,0)
 

 

The ifnull adjust for dates where there were no sales

 
The value on Start Date is simply
 
IFNULL({ FIXED [Order Date]:   sum( if DATETRUNC('day',[Order Date]) = 
DATETRUNC('day',[Enter start date])  then    [Sales] end )} ,0)
 
 
And the percent difference is:
 
If (sum([JD value at lookback date]))=0 then 1 
elseif (sum([jd value on start date parameter value]))=0 then 0
else(sum([jd value on start date parameter value])-  sum([JD value at lookback date]))/sum([JD value at lookback date])
end
 
 
All the calculations are LOD's and can be used to create a simple summary table 
 
 
 
Determining the total sales over the "lookback period" will require another LOD
 
IFNULL({ FIXED :sum(
if [JD Fixed back date from param]<= [Order Date] and 
[Order Date]<=[Enter start date]
then [Sales] end)}  ,0)
 
 
Lots of LOD's and can be confusing but necessary to avoid table calculations that are based on a position in a table and not an actual date.  
 
There could be many more examples and variations on viewing sales by business day. I encourage you to try some of your own – 
 
The examples here are captured in a workbook that has been posted to my public site at 
 
Jim
 

 

Leave a Reply

Your email address will not be published.