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

Some users want to look at how their average sales vary by the day of the week

 

 

 

 

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

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 

https://public.tableau.com/profile/jim.dehner#!/vizhome/Salesbyweekday/Dashboard1 

 

Jim