Need of rolling calendar

As I stated in an earlier post, a rolling calendar is a better visualization tool than monthly calendar, especially for non-financial and dynamic data monitoring.

But its design is not so obvious using Tableau. Here I am going to lay out step by step the design of a rolling calender. By calendar, I mean a calendar based heat map.

A 5-week range is exactly 35 days. The judicious choice is such that it covers any month's length. And a monthly calendar is just a subset of it. However you can pick any window size, greater or smaller than 35 days. And you can make it a parameter if you wish.

Step 1. Filter design

The rolling calendar is by default set to the latest 5 weeks of the report date, not the system time. On the other hand, it must also allow a user to check any historical data in an arbitrary 5-week window.

A calendar grid is not as flexible as axis-based charts. That's the reason we chose to make the window size fixed.

Basically, there are need for 2 date filters. Upon a switch, we can filter dates to be either the default last 5 weeks or any 5 consecutive weeks. Some may ask that, doesn't the any 5-week filter include the last 5 weeks? Unfortunately, an arbitrary (parameterizable) date range filter can't be made default to the latest date in Tableau.

The two-option date range filter is a technique I shamelessly borrowed from Andy Kriebel's post:

In Andy's design, the two options are: full date range and an arbitrary limited date range. In axis-based chart, he has got the luxury of using two options of vastly different scales. But in calendar grid, it's much less flexible.

Step 2. Selector design

There are two modes to select: the last 5 weeks or any 5 weeks. So we need a binary parameter like this:
[Select a filter]:
True:Last 5 weeks
False:Pick an end date for any 5 weeks


The first one is the default option. I use the following calculation to get the last date in the report dynamically:

LastDate=rawsql_date("select max(report_date) from log_data_table")

In the illustrative example below, I defined LastDate using a fixed date because the workbook is not connecting to any relational database.

The date range filter to get the last 5 weeks is:
Date>=LastDate-34 and Date<=LastDate


Then we need to duplicate the data source, and use Date of the second data source as the date filter. To be exact, use it as the end date for the date filter. Make sure that the date is continuous.


In Andy's selector design, he qualified his selection logic as magic. It took me to the top of Santa Cruz mountain to crack that magic while on a weekend hiking trip. I thus formulated my own logic which is non less magic. Do remember to set the filter to be at least 1.


Int(
([Select a filter] and
min([REPORT_DATE])>max([Last Date])-35)
and
max([REPORT_DATE])<=max([Last Date]))
or
(not([Select a filter]) and
min([REPORT_DATE])>max([Sheet2 (CalendarTest) (copy)].[Date])-35)
and
max([REPORT_DATE])<=max([Sheet2 (CalendarTest) (copy)].[Date])
)



Step 3. Calendar design

Again, please refer to Andy's post for a heat map calendar design. The difference here is, instead of using week numbers that will leave some gaps between the years around January 1st, I use a technique that is using the first day of the week as the week index. So we will get a continuous grid of weeks. It is done by defining a calculated field: [Week Of] using the formula such as

Datetrunc('week',Date)



Step 4. Dynamic Title

The rolling calendar is limited in displaying its date range. In monthly calendar, we can handily display month in the title. In our rolling calendar, we need a dynamic title to show the date range in the calendar. Thus I created two calculated fields for the start date and the end date in the view:


-#FilterEndDate:
if [Select a filter] then max([LastDate])
elseif max([Sheet2 (CalendarTest) (copy)].[Date])<=max([LastDate])
then max([Sheet2 (CalendarTest) (copy)].[Date])
else max([LastDate])
end

-#FilterStartDate:
[FilterEndDate]-34

Drag -#FilterStartDate and -#FilterEndDate into the detail area first (not in the detail, but under it). In the Title Editor, we need to insert a line like this:
Date Range: <AGG(FilterStartDate)> - <AGG(FilterEndDate)>

Then we get a dynamic title showing the date range in sync with date slider picker. Note that we have to set the default date format for both FilterEndDate and FilterStartDate.

The result

And voila. The same technique can be applied to the design of line/bar charts with a fixed-size rolling time window that default to the latest date, even after a server refresh.