Tableau - How to Compute and Filter Prior Business Day

Brian PringleDigital Services Advisor, SCM, ERP
Published:
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Objectives:
 
Use this procedure to filter data by the last business date rather than the maximum or current business date.
 
Background: 
 
Tableau has the ability to display the maximum and minimum values for data, including date fields.  It also has the ability to filter by date ranges or relative dates – such as today or yesterday.
 
The problem is that it is sometimes necessary to compare data from the current business day to the prior business day.  The existing “yesterday” filter works if the business operates seven days a week or if the report is generated Tuesday through Friday.  However, if the report is generated on Monday and the business did not have any data from the weekend, then no data is returned in the report.
 
To work around this issue, it is necessary to create some calculated fields to figure out the current business day and the previous business day.  After finding those dates, it is necessary to filter the data based on the prior business day.
 
Logic:
 
It is possible to calculate the prior business day based on the day of the week.  For example, one could state that the prior business day for Monday is Friday for a business that is only open on weekdays.  However, this logic breaks when Monday is a holiday because Tuesday will reference Monday as the prior business day.
 
Instead, it is better to find the maximum business date that is less than the reference date.
 
Note:  This procedure assumes that the data source has a timestamp field that shows when the data was entered in the database.  For this procedure, the timestamp is called [Compare Date].
 
Example: 
 
Given the following distinct datetime stamps, it is easy to see the maximum date and the highest date prior to the maximum.
 
2016-03-30 00:00:0000
2016-03-31 00:00:0000
2016-04-01 00:00:0000
2016-04-04 00:00:0000
2016-04-05 00:00:0000
2016-04-06 00:00:0000
 
In the above example, the maximum date is 2016-04-06 (Wednesday) and the prior work day is one day less, or 2016-04-05 (Tuesday).  However, if one attempts to compare 2016-04-04 (Monday) to one day less, it would result in the date of Sunday -- not Friday. 
 
To get the prior business date, it is necessary to look at the current date being evaluated and the maximum date less than that date.   If one references 2016-04-06 (Wednesday), the maximum date that exists in the set less than that date is 2016-04-05 (Tuesday).  If one references 2016-04-04 (Monday), the maximum date that exists in the set less than that date is 2016-04-01 (Friday).  This procedure would also account for days that are not recorded, such as holidays.
 
Level of Detail (LOD) Functions:
 
Because Tableau functions solely by issuing SQL commands, it is necessary to perform a subquery.  However, Tableau does not support subqueries without creating a custom SQL statement at the data source.  For this to work, it is necessary to manipulate the “Level of Detail”, or LOD functions in Tableau. 
 
The LOD functions allow the workbook to change the granularity of an aggregated field (SUM, AVG, MAX, or MIN).  We will use the LOD function to find the maximum date less than the current date and will then return that as a date field.
 
Procedure: 

1. Attach the necessary data sources and configure the relationships as needed.

2. Create a calculated field called "Calc_LastBusinessDate".  This field is going to be used to hold the date for the previous business date.
 
This field will get a list of the dates less than today(), get the maximum value from that list, and then calls it [Compare Date].
 
Formula:
 
{fixed: max(if [Compare Date] < today() then [Compare Date] end )}

2.png
This field holds the value of the date for the previous business day, but cannot be used to filter data dynamically.

3. Create another calculated field called “Calc_IfLastBusinessDate”.  This field is going to be used to compare if Calc_IfLastBusinessDate is equal to the [Compare Date] field. 

Formula:
 
[Calc_LastBusinessDate] = [Compare Date]
 
3.pngThis field becomes a Boolean expression and CAN be used to filter the data dynamically.

4. Add the Calc_IfLastBusinessDate field to the "Filters" container. 

5. Edit the newly added filter.  Select True and then click OK.
5.png
6. The data displayed in the worksheet will now be filtered based on the previous business date. 
 
This procedure is complete.
 
1
5,955 Views
Brian PringleDigital Services Advisor, SCM, ERP

Comments (2)

Brian PringleDigital Services Advisor, SCM, ERP

Author

Commented:
Tableau does not have a good set of business days.  It will calculate today and yesterday or a range of dates relative to today, but calculating based on another anchor date is not intuitive.  There are numerous questions on the Tableau Web site asking for this.  The reason that we are not using an actual table to store the dates is that we may not always have business on the weekend and other times might.  The intention of this procedure is to let the system figure out when the last business date was from the data rather than a set calendar.

The data set has a large amount of data that gets imported during each business day.  I can query the database to get the distinct list of dates and then calculate from there.  The LOD functions in Tableau are the closest thing to getting that information.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Very well written.  Voting Yes.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.