FAQ Series - Business Days

 

My blog has moved to jimdehner.com 

Thank you for all the support over the past 2 years - I recently updated my blog to make it easier to read, improve the rendering of images and tie back to video presentations that are associated with the content - Check it out at  Jim's new blog  -   

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 us the US standard Sunday - Saturday week with a Monday - Friday "Business Week"  - 

1-Weekday Labels

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

A straightforward way is 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 is  easy to get - but how to 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  https://jimdehner2.blogspot.com/2019/12/yoy-three-different-approaches.html )

3-Holidays

Holidays, including those that are unique to 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 prior to 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 own workbook - also it determines the number of calendar days to look back based on the business need to express the look back in business days - 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 the I wanted to 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 weekday 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 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 weekend 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 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 which 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