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 quotesDATENAME('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
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 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
Jim
