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.


0

Add a comment

(Refresh the page if you want to view the gif image multiple times. Or go to Tableau Public and click the button at the top-right corner.)

Jake and I collaborated on a dashboard. He told me that he learnt a way to create an in-place help page in Tableau. He first saw it at a conference somewhere and couldn't recall who the speaker was. So I am blogging here about it but the credit goes to somebody else. If anyone knows who the original creator is, leave a comment below.

The key idea is to float a semi transparent worksheet on top of the dashboard, where a help text box is strategically placed on top of each chart. This way, we can explain how to view each chart and what data points are important, etc. This worksheet is collapsible by a show/hide button. 

Below I would like to show how this worksheet can be constructed.

1. Sheet with a single data mark.

  • Double click the empty space in Marks panel and add two single quotes. Make the null pill a text label. This creates a single null mark.
  • Set the view as "Entire View"

2. Create an show/hide button

  • Go to the target dashboard
  • Drag a floating vertical container to the dashboard, making it cover all the area of interest.
  • Drag the Single Null Mark sheet and drop it into the above container. Hide the sheet title.
  • Create an open/close button for the container and place the button at the top-right corner.

3. Add annotations

  • Format the sheet background opacity as 70% in the layout manager             
  • Select area annotations and place them anywhere of interest. 
  • Write help text and format it to highlight important messages.  
  • The text can serve as functional guide and/or insight guide.

Here is an example. Feel free to download the workbook and explore. Click the "i" button at the top-right corner to view the in-place help. 

0

Add a comment

Blog Archive
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.