One Set of Date Filters to Rule Them All

If you are a Tableau and Salesforce analyst/administrator, this post will likely resonate with you. Salesforce data sets tend to have more than a few date fields in them. That being said, it’s definitely not exclusive to that particular context and use case. Here goes…

The Problem:

You have a data set that contains multiple Date fields. You are creating several views for your dashboard, each of which uses a different date field to identify trends over time. On your dashboard, you don’t want to have several date filters…one for each view. Having multiple date filters on a singular dashboard causes clutter and confusion for your audience. However, since each view uses a different date field, adjusting one of the filters does nothing for the rest of the views that don’t contain that particular date field.

The Question:

How can I get all my views to be controlled by one filter, or in this case, one Begin Date filter and one End Date filter?

The Solution:

Create two parameters and several calculated fields (details of which are below) that allow you to control all of your date fields, and thus, the entirety of your dashboard.

First of all, you need to create two Date parameters, where you manually adjust the Minimum value to match the earliest date value in your entire data set. You’ll want to leave the Maximum value untouched, allowing it to adjust to whatever your audience chooses. Then change the Step Size to “1 Months”.

Secondly, you’ll need to create the following calculated field for each of your views on your dashboard:

[Date Field] >= [Start Date_Universal Filter] AND [Date Field] <= [End Date_Universal Filter]

Note: You need one of these calculated fields for each view. Use the particular date field that pertains to each view as your [Date Field].

For a step by step overview, here’s a quick how-to video using the Superstore data set for your viewing pleasure…

 

Thanks for taking a look. Hope it was helpful

-Bill

Expanding the Tableau Data Pipeline: Auto-creation of tde from csv

The scenario

Imagine that you’ve pulled some data out of a database (choose your flavor) and want to now analyze in Tableau. You notice, however, that it’s too massive to tinker with in Excel (yes, this actually happened).

So, you think: “Wouldn’t it be awesome to have Tableau generate a shared data source for me if I just point it to a csv and then choose what datatypes are in it?” At that point, you can analyze to your heart’s content. Bonus: it’s repeatable and, if your csv updates, so will your data source.

We can now use this as part of our data pipeline: we write some awesome query and want to share the results with our colleagues. Instead of sending a csv file to the crew, we just send them a Tableau data source.

The setup

You will need 2 config/base Tableau files (we’ve included them here):

  • XML file with the structure for a TDS
  • Basic/simple/dummy TDE file (really, it’s not dumb at all, as TDEs are amazing; rather, it’s just a basic ‘helper’ TDE we’ll use to package with the TDS for the TDSX)

csv_to_tde_1

 

csv_to_tde_2

The script (see our github repo for the script):

  • Reads your csv file
  • Let’s you choose what data type you need for the columns; if you want to let Tableau work its magic, just set the ‘Choose Data Type?’ to false and the Extract engine will come to the rescue.
  • updates the XML in the TDS
  • Packages the ‘helper’ TDE with the update TDS
  • Publishes to Server and refreshes with the new data and new file

 

csv_to_tde_3

 

 

Don’t believe us? Watch the video (this is where we’ve set the ‘Choose Data Type’ to True)…

 

 

A Practical Example

With the ability to search the web for interesting and varied data sets, we run into csv’s a lot. The NYC Taxi data is no exception

So, we grabbed a month’s worth of data (approx 2gb) and pulled into Tableau (see image below) and in 180 seconds (this was for the refresh on 12 million rows; the script took less than 3 seconds), it was a shared data source.

Another version of this example exported all the months, merged them and then made a data source on Tableau. Either way, all one needs is a csv file.

taxi_data

 

 

We’ll update this in future releases to use tables/custom sql as well as make it a more robust pipeline.

 

CsvTdeCreator_Master

TdeContent