Date slider in Tableau provides an easy way to refine date period displayed in Tableau viz. However, when speaking of large volumes of data, for the sake of performance, it might be necessary to display default date slider range for shorter time coverage. This is especially true for events analytics which require certain actions to be based inpromptu.

The problem

Problems start with viz design. There is an option to setup date filter as Relative but this option will ‘freeze’ end date for today. Therefore, same period analytics in history will not be available. How about absolute filter? If you set it to 30 days (even if you alter only one end), then .twb(x) file will have default date slider range fixed for good:

30 day slider Tableau JS API

Note the syntax Tableau uses in the file. You will find it familiar in a moment.

It looks like, that with the current setting it’s impossible to have both features in one dashboard: to have last 30 days shown when dashboard is loaded and to have fully fledged date slider you can play with.

The solution

This is where Tableau JS API comes handy. Actually it’s a pretty nice hack which was presented to me by Bryant Howell in his blog post here https://tableauandbehold.com/2017/02/02/defaulting-to-today-relative-date-on-a-date-range-filter-in-tableau/ . His solution was almost complete, however, in my case required couple more tweaks to work without issues.

Overview

General idea is to use Tableau JS API on existing dashboard in a way that it will change default filtering once and then allow user to change dashboard however needed. It’s fairly easy task when dashboard is being embedded on customer portal of any kind or filter is being provided from outside. In this case, I needed to have Javascript logic embedded in the published dashboard.

The hack is based on Tableau Server capability to import web data connectors which are nothing more than Javascript / HTML files containing instructions to fetch data from third party services. If one can use script to import data why not run Tableau JS API commands itself?

Required files

Script that we are about to use, requires couple .js files to run. They are available without any problems over the Internetz, so you shouldn’t have problems downloading/referencing them. We need:

  • jQuery.js file
  • Moment.js file

jQuery.js is a library for the jQuery (duh!) which is a fast, small, and feature-rich JavaScript library you can use to manipulate your pages in HTML. It can be downloaded from jquery.com or you can reference it directly in the script (as I did).

Moment.js is a library which makes date manipulation in Javascript easier. It can be downloaded from momentjs.com.

You can download / reference either *.min.js or regular *.js versions of the files. In most of cases they differ only in file size.

Importing Web Data connectors

For Tableau Server then the easiest way to make your script files available in the viz, is to simply import then to Tableau Server directory and then refer to then directly on the Viz. In our case, files that would need to be imported are lastperiod.html and moment.min.js file. To do so, you need to have access to machine where Tableau Server is installed, copy files to the temporary folder, run cmd.exe, navigate to tabadmin location (most likely c:\Program Files\Tableau\Tableau Server\10.x\bin\) and fire up commands:

tabadmin import_webdataconnector d:\fileLocation\lastperiod.html --overwrite
tabadmin import_webdataconnector d:\fileLocation\moment.min.js --overwrite

after each prompt you should receive confirmation:

30 day slider Tableau JS API

For Tableau Online it might be difficult to use such webconnector as most likely running it will cause cross-origin issue..

Using Tableau JS API

When we have moment.min.js file uploaded to server, we can start writing the code that will do the magic. Here’s what worked for me (click to expand):

<!DOCTYPE html>
<html>
    <head>
        <title>Set Date Filter to Last N Periods </title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
        <script src='moment.min.js'></script>
        <script type="text/javascript">

//Author: mlazecki - www.meowbi.com | please don't remove this copyright
//usage - add URL in tableau
//servername/webdataconnectors/lastperiod.html/FieldName&DatePart&PeriodsBack&[alwaysRun]
//alwaysRun defaults to 0 meaning that it will run only once for each tab in one session
//http://servername/webdataconnectors/lastperiod.html?FieldName=Session Start&DatePart=days&PeriodsBack=30&alwaysRun=0

   			var viz;
            var current_sheet;
            var alreadyClicked;

   		function getTableau() {
   			console.log("getTableau");
		     return parent.parent.tableau;
            };
            
        function getCurrentViz() {
        	console.log("getcurrentviz");
              return getTableau().VizManager.getVizs()[0];
            };

	
		function lastdays(filtername,date_part,periods_back){
		  	var now_max = moment();
		   	var now_min = moment().subtract(periods_back, date_part);
	
		console.log("starting lastdays");

			viz = getCurrentViz();
			workbook = viz.getWorkbook();
				sheet=viz.getWorkbook().getActiveSheet();
				worksheetArray = sheet.getWorksheets();
				worksheets = sheet.getWorksheets();

						for (var i =0;i<worksheetArray.length;i++){
//console.log("assign current_sheet");
							 var current_sheet = worksheetArray[i];
//console.log("apply filter: " + filtername + now_min.toDate() + now_max.toDate());
		                     current_sheet.applyRangeFilterAsync(filtername, 
		                     	{
		                     		min: now_min.toDate(), 
		                     		max: now_max.toDate() 
		                     	}
		                     	).then(
		                     		function(f){},
		                     		function(err){console.log("error happened");}
		                     		);

						}
		}
            

		function gup( name ) {
			console.log("starting gup with " + 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 (){
         	
		         	viz = getCurrentViz();	
		           	workbook = viz.getWorkbook();
		           	sheet=viz.getWorkbook().getActiveSheet();
		          	

//check if the alwaysRun variable is set in the link    
				//	console.log("check if gup is a null string: "+gup('alwaysRun')=="") ;


				           	if (gup('alwaysRun') ==1){
				           		alwaysRun = 1;
				           	
				           	} else {
				           				alwaysRun = 0;
				           			}

				           	console.log("alwaysRun: "+alwaysRun);


// alwaysRun = 0 -> add to session storage and run
// alwaysRun = 1 -> dont add and run
			

					//console.log("testu");

					//check session storage if current sheet is already there
												for ( var i = 0, len = sessionStorage.length; i < len; ++i ) {
														if (sheet.getName()=== sessionStorage.getItem( sessionStorage.key( i ) )){
																alreadyClicked = 'changed';
																console.log(alreadyClicked);
																break;

															} else 	{
																alreadyClicked="none";
																console.log(alreadyClicked);
															}
												} 
										
									
														if (alreadyClicked==='changed'){
																
															console.log("i'd run this script already for this worksheet in this session");
																return false;

															} else {

														               var filtername= gup('FieldName');

														               if (filtername == ""){
														               	    	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'));
														                	}
														               	
							// add to session storage depending on alwaysRun parameter

														if (alwaysRun!=1){
																
																		sessionStorage.setItem(sheet.getName(),sheet.getName());
																		console.log("session size:" + sessionStorage.length);
																}
																
																		for ( var i = 0, len = sessionStorage.length; i < len; ++i ) {
																			  	console.log( sessionStorage.getItem( sessionStorage.key( i ) ) );
																			}

										//console.log("passing to last days:" + filtername);
														                lastdays(filtername, date_part, periods_back, alwaysRun);
														 			}
													
					}

         );

        </script>
    </head>
</html>

Note that in the script there is not reference to Tableau API? This is because we are not embedding dashboard on the custom .html but we are in fact all the time in Tableau environment.

This script can be copy-pasted to lastperiod.html file directly without any changes or you can download the file here:

lastperiod

Once you have the file updated, you need to repeat import action for the lastperiod.html file described in the previous point.

How does it work?

If you are no Javascript Guru, then this script might look Greek to you. Let me split it to smaller chunks and describe what is actually being done here. Starting from the end:

 $( document ).ready(
            function (){
         	
		         	viz = getCurrentViz();	
		           	workbook = viz.getWorkbook();
		           	sheet=viz.getWorkbook().getActiveSheet();
		          	

//check if the alwaysRun variable is set in the link    
				//	console.log("check if gup is a null string: "+gup('alwaysRun')=="") ;


				           	if (gup('alwaysRun') ==1){
				           		alwaysRun = 1;
				           	
				           	} else {
				           				alwaysRun = 0;
				           			}

				           	console.log("alwaysRun: "+alwaysRun);


// alwaysRun = 0 -> add to session storage and run
// alwaysRun = 1 -> dont add and run
			

					//console.log("testu");

					//check session storage if current sheet is already there
												for ( var i = 0, len = sessionStorage.length; i < len; ++i ) {
														if (sheet.getName()=== sessionStorage.getItem( sessionStorage.key( i ) )){
																alreadyClicked = 'changed';
																console.log(alreadyClicked);
																break;

															} else 	{
																alreadyClicked="none";
																console.log(alreadyClicked);
															}
												} 
										
									
														if (alreadyClicked==='changed'){
																
															console.log("i'd run this script already for this worksheet in this session");
																return false;

															} else {

														               var filtername= gup('FieldName');

														               if (filtername == ""){
														               	    	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'));
														                	}
														               	
							// add to session storage depending on alwaysRun parameter

														if (alwaysRun!=1){
																
																		sessionStorage.setItem(sheet.getName(),sheet.getName());
																		console.log("session size:" + sessionStorage.length);
																}
																
																		for ( var i = 0, len = sessionStorage.length; i < len; ++i ) {
																			  	console.log( sessionStorage.getItem( sessionStorage.key( i ) ) );
																			}

										//console.log("passing to last days:" + filtername);
														                lastdays(filtername, date_part, periods_back, alwaysRun);
														 			}
													
					}

         );

 

$(document).ready part is responsible for firing up functions as soon as the page is loaded. In this case, on page load it is supposed to run gup function and lastdays function (both described below). On top of that sessionStorage variable is added, so the script is not run everytime something changes on the viz (this would make you crazy, trust me).

	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];
		}

gup function is the feature that make things more automated – credits goes to NetLobo http://www.netlobo.com/url_query_string_javascript.html. This function fetches information from the WebPage URL and passes it to the function responsible for setting default date slider range. It will became clearer in the Viz implementation phase.

function lastdays(filtername,date_part,periods_back){
		  	var now_max = moment();
		   	var now_min = moment().subtract(periods_back, date_part);
		
			viz = getCurrentViz();
			workbook = viz.getWorkbook();
				sheet=viz.getWorkbook().getActiveSheet();
				worksheetArray = sheet.getWorksheets();
				worksheets = sheet.getWorksheets();

						for (var i =0;i<worksheetArray.length;i++){
							 var current_sheet = worksheetArray[i];
		                     current_sheet.applyRangeFilterAsync(filtername, 
		                     	{
		                     		min: now_min.toDate(), 
		                     		max: now_max.toDate() 
		                     	}
		                     	).then(
		                     		function(f){},
		                     		function(err){console.log("error happened");}
		                     		);

						}
		}

lastdays function does all filtering magic. It fetches min and max date values based on what gup function provided and passes it to applyRangeFilterAsync()  function which is the Tableau JS API function. You can read more about it in the Tableau API Reference here: https://onlinehelp.tableau.com/current/api/js_api/en-us/JavaScriptAPI/js_api_concepts_filtering.htm. Btw: can you see min and max syntac for applyRangeFilterAsync()? It looks similar to .twb(x) structure, right?

function getTableau() {
   			console.log("getTableau");
		     return parent.parent.tableau;
            };
            
        function getCurrentViz() {
        	console.log("getcurrentviz");
              return getTableau().VizManager.getVizs()[0];
            };

getTableau() and getCurrentViz() functions are responsible for fetching Tableau / viz objects so they can be used in the script.

Note that this script does not evaluate if the filtered view is a sheet, dashboard or a story. It assumes that it’s a dashboard as most of the Vizualizations work this way. If you are displaying single sheet and would like to have it filtered this way, you would need to add custom check on

Setting Default date slider range for Viz

Knowing everything about script, it’s time to implement it onto Tableau Dashboard. All you need to do is pull Web Page object to your dashboard.

30 day slider Tableau JS API

It can be set to Floating and 0px. Nothing will be displayed there as the page itself contains only script, there’s not content. In the Web Page address enter:

http://servername/webdataconnectors/lastperiod.html?FieldName=DateField&DatePart=days&PeriodsBack=30&alwaysRun=0

What is really important here is the servername. It must exactly (and I mean exactly) the address you see in the Web Browser upon publishing the viz. Otherwise, you will receive cross link error from API.

Note that there are 3 parameters included after quotation mark. This is where gup function from the script kicks in. You don’t have to add separate lastperiod.html files for each of the reports – you just change the parameter.

Parameters:

  • FieldName – this is the name of the slider you would like to have data filtered on. Check the Filter Prerequisites section below for naming convention
  • DatePart – indicates whether you would like to go back using days, months or other date part acceptable by Tableau
  • alwaysRun (optional) – allows you to set dashboard to be always refreshed. By default sheet’s slider is changed only once per browser tab session. This means that when you change the tab and get back you will still have selected dates visible. Sometime tough, you might want tab to always default to last x periods. In this case, you should set this parameter to 1

DatePart accepts not only days, but months and years as well making it more flexible. Filling this part can be tricky as fields are not always what they seem. Please see Filter Prerequisites section below to make sure you do it right.

Publish the dashboard on the same server as you provided in the servername, open the dashboard in the browser and voila! You have your date filtered!

Filter Prerequisites

As this script does most of the work for you, it may not work exactly from the beginning. After many hours trying to debug this script, I’ve come to conclusion that there are few prerequisites that must be fulfilled for this solution to work:

  • DateField name must be consistent with the name you see in the Edit Filter Dialog not with the name on Dashboard.

30 day slider Tableau JS API

  • DateField you reference to in the URL must be placed on the filter shelf of the sheet you’ve selected it from on the dashboard. If you have only one sheet then it’s not a problem but when using multiple sheets on single dashboard you must make sure that slider field is on filter shelf. Not a calculated field based on this field – this field exactly.
  • Filtering granulation must be the same for URL parameter and Field. If you use DatePart = days then DateField on filter shelf should be in the day granulation. Meaning, year(DateField) is no good.
  • filter type must be set to Range of Dates not Special:

30 day slider Tableau JS API

Troubleshooting

I’ve come across couple of strange behaviors using original script and needed to adapt it to own needs. If script does not work for you then see the suggestions below but it might require you to rework the script itself..

  • Filter works but range is only limited to the filtered range (there’s not free slider in the past to play with) – check filters prerequisites, especially granulation point
  • Filter is not working – check filter prerequisites section – especially filter name. If doesn’t help, use browser console to see what’s going on. Before loading the page right click in the server project page and select Inspect, Console. You might find out that there’s a problem with Server.
30 day slider Tableau JS API 30 day slider Tableau JS API
  • I changed the last 30 days filter, refreshed the page and still got this new range – it’s not a bug, it’s a feature. This prevents script from running multiple times in one session. Open dashboard in new tab and it should work
  • The page you are looking for could not be found – check if the web page URL does not contain spelling mistakes and refer to webconnector importing section to make sure that .html file is present on Server30 day slider Tableau JS API

 

Update 16 Feb 2017: added functionality for multisheet dashboard so if you changed date slider on a sheet it won’t be changed back to last x days when you swtich tabs within one session.

Update 16 Mar 2017: added alwaysRun Parameter allowing to refresh date slider every time viz is loaded or only once per browser’s tab session