Defaulting to Today (Relative Date) on a Date Range Filter in Tableau

Tableau has two different type of Continuous Date Filters: Relative Date Filters and Date Range filters. While it’s easy enough to switch between them in Desktop, there is no way to flip a Relative Date filter into a Date Range Selector in a view published in Server (without using Web Edit). What if you’d like a default date range relative to today to be selected, but give the user the option to make their own selection?

The classic solutions have involved parameters , but if that is too clunky, here’s a new solution, based on Tamás Földi’s fantastic solution for using the JavaScript API within a published dashboard. A similar solution could set Parameters to a default value rather than Date Range filter. You could even pipe the Tableau Username back into the viz as a parameter automatically for use as a Parameter in Custom SQL (not secure, but at least automatic).

The solution is an HTML page that utilizes the Tableau JavaScript API and the Moment.js library to figure out the dates relative to today in JavaScript, then sending them to the Tableau viz via the JS API applyRangeFilterAsync() method. By including this page in the dashboard, you can do any custom logic to any of the filters present.

I’ve written a generic HTML page which should work with any dashboard, available on GitHub. Download this page to your machine, then you’ll need to publish it and the moment.js file as a Web Data Connector using tabadmin.

Installing JS API Page as Web Data Connector

Launch up cmd.exe as an Administrator. Now go to your Tableau Server bin directory. Do the following two commands (adjusting your path for where you stored the files:

C:\Program Files\Tableau\Tableau Server\10.1\bin>tabadmin import_webdataconnector “C:\{filepath}\relative_date_with_data_selector_embed_within.html” –overwrite

C:\Program Files\Tableau\Tableau Server\10.1\bin>tabadmin import_webdataconnector “C:\{filepath}\moment.js” –overwrite

This places the files actually on the Tableau Server in a directory that is accessible via standard URLs. Remember to use –overwrite if you need to make any updates later. You may see a message about “this method being deprecated”, but everything will work. I don’t have any idea when it will really stop working / be deprecated, but you can just manually drop files in the correct directories if you need to, so there will still be ways to incorporate these files.

Using the page in a Dashboard

Open up Tableau Desktop and load the dashboard you want to give a “relative date default” to. To add in the JS API page, drag out the web part to the dashboard. You might even choose for it to be Floating, and shrink the page down to almost nothing. The HTML page itself is blank so it won’t show up as anything but white space.

web-page-object

Now put in the URL of the page we published as a web data connector. The format will be

http://{domain name}/webdataconnectors/relative_date_with_data_selector_embed_within.html

Then fill out the three URL parameters which control how the filter will apply (in any order).

  • FieldName: This is the name of the field the filter will apply to
  • DatePart: This is the type of date part that will be subtracted. These are directly the same as used in the moment.js libary; they are lower-case and have an ‘s’ at the end like: ‘days’, ‘months’, ‘years’
  • PeriodsBack: The number of DateParts to subtract from Today.

relative_date_url_params

So the whole thing from one month back from today would be:

http://{domain name}/webdataconnectors/relative_date_with_data_selector_embed_within.html?FieldName=Some Field&DatePart=months&PeriodsBack=1

Obviously, the moment.js library has more options, so you could implement something to give you Month-to-Date, Year-to-Date, etc.

One other thing to note: You need to use the Fully Qualified Domain Name of the Tableau Server that the end user will type in. In local testing, if you publish with 127.0.0.1, but go to Tableau Server via localhost, the JS API will be locked out due to Cross-Domain JS Protections. Everything needs to match up the inner page to have access to the JS API of the main viz. This isn’t as much of an issue in production as in testing.

Publish to Server

Once this is published to Server, when you load the up the viz, the standard Date Range selector will be updated by the JS API to the default period you put in programmatically, but it will be fully available for the end user to change.

How it Works

You should read the StarSchema blog for the essence of how you are able to access the JS API object of the viz. Once you have access to that, setting the filter is a relatively simple process.

The setFilterToRelative function does the majority of the work. I know it seems like a lot is going on here with those case statements, but they are best practice to handle whether the getActiveSheet() method is giving you a Worksheet or a Dashboard (see this intro to the quirks of the Tableau JS API). In the end, both code paths are simply doing an applyRangeFilterAsync(field_name, {min: now_min.toDate(), max: now_max.toDate() }). now_min and now_max are the moment.js objects created to do the correct date part subtraction.


function setFilterToRelative(field_name, date_part, periods_back){
viz = getCurrentViz();
workbook = viz.getWorkbook();
sheet = workbook.getActiveSheet();
var now_max = moment();
var now_min = moment().subtract(periods_back, date_part);
// This handles whether you have a sheet, a dashboard or a story

switch (sheet.getSheetType()) {
case 'worksheet':
console.log('I am a worksheet');
current_sheet = sheet;
total_sheets = 1;
current_sheet.applyRangeFilterAsync(field_name, {min: now_min.toDate(), max: now_max.toDate() }).then(
function (f){
}
);

break;
case 'dashboard':
console.log('I am a dashboard');
db_objects = sheet.getObjects();
worksheets = sheet.getWorksheets();
// Loop through all the sheets of the dashboard
for(i=0;i<worksheets.length;i++){
var sheet_name = worksheets[i].getName();
console.log("Looking for filters on sheet: " + sheet_name);
// Skip any sheet names in the sheets_to_skip array
if (sheets_to_skip.indexOf(sheet_name) !== -1){
console.log("Sheet Named: " + sheet_name + " found, skipping");
continue;
}

// Increment the global total sheet count AFTER we skip
total_sheets++;
console.log(total_sheets + " sheets found so far");

var current_sheet = worksheets[i];
current_sheet.applyRangeFilterAsync(field_name, {min: now_min.toDate(), max: now_max.toDate() }).then(
function (f){
}
);
}
break;
case 'story':
console.log('I am a story, not going to do anything');
return false;
break;
}

}

Then you have to actually run this code when the page loads. This is handled by the following


// Gets a given URL parameter value
function gup( name ){
name = name.replace(/[\[]/,"\\\[").replace(/[\]]/,"\\\]");
var regexS = "[\\?&]"+name+"=([^&#]*)";
var regex = new RegExp( regexS );
var decoded_uri = decodeURIComponent(window.location.href);
var results = regex.exec( decoded_uri );
if( results == null )
return "";
else
return results[1];
}

$( document ).ready(function (){
// Set up URL parameter reading for total flexiblity
// FieldName, DatePart, PeriodsBack
// Defaults to 1 month
var field = gup('FieldName');
if (field == ""){
return false; // Exit if no field specified
}
var date_part = 'months';
if ( gup('DatePart') != ""){
date_part = gup('DatePart');
}
var periods_back = 1;
if ( gup('PeriodsBack') != ""){
periods_back = parseInt(gup('PeriodsBack'));
}
setFilterToRelative(field, date_part, periods_back);
});

There are two parts here — the gup function for getting the specified URL parameters which I got from Stack Exchange linking to NetLobo and then the $(document).ready jQuery onload. This simply parses the URL you’ve embedded in the dashboard to do the correct actions for that given dashboard. One file, any number of dashboards.

 

 

Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s