Parameter Actions + Geospatial Functions = Tableau Awesomeness!
The very first blog post I ever wrote demonstrated how to calculate distance between locations using a parameter and table calculations in Tableau. Back then, parameter values could only be changed via drop down or slider controls, they weren’t dynamic, and you could only reasonably set a single value per parameter. But Tableau has grown by leaps and bounds when it comes to geospatial functions. Parameter actions are a game changer in every way. Using them in combination makes for Tableau Awesomeness!
Tableau Parameter Actions are a Game Changer
Two capabilities of parameter actions specifically encourage creativity:
- The ability to trigger an action from a view and have that action change the same view
- The ability to append values to a parameter in such a way that you can capture a history of interaction (plus other data!)
Lindsey Poulter has been tweeting about many amazing things that can be accomplished with parameter actions (especially in combination with Viz Animations that are currently in beta) and she’s demonstrated the power of these capabilities. I leveraged the capabilities in Tableau Minesweeper (watch the 30 minute mark here to see some details of how it works). And I’ve been daydreaming about how I could have used this for Tic Tac Toe or Battleship and all the Tableau games yet to be made!
But I’ve also been excited to think through some practical use cases and the ability to dynamically draw flight paths and calculate distances based on user clicks has me excited! Armed with a data set from the Global Airport Database, I set out to make a dynamic tool to build flight paths…
Using Tableau Parameter Actions to Store Data
The ability to store and read a sequence of user input turns Tableau into a nearly full-fledged programming language! In this example, the basic idea is to use a parameter action to build an ever growing string of airport codes along with the latitude and longitude. There are 3 basic components to this technique:
- The parameter to store the data
- A calculated field to append new data to the existing parameter value
- A parameter action to update the parameter with the calculated field value.
Here’s how it would work in an example view with 3 marks having separate values:
Prior to any user interaction, the 3 marks have separate values A, B, and C. The Parameter value is blank. The calculation returns separate values for each of the marks – a blank + “|” + the value for the specific mark. That calculated value represents a possible value to be stored based on which mark the user selects.
Let’s say the user selects B:
The calculated value, |B, is now stored in the parameter. The calculated value is updated for each of the marks, representing the next set of possible values to be stored in the parameter. But only the value for the next mark clicked will be stored. Let’s say that is mark C, causing the value |B|C to be stored in the parameter:
Now the calculation is showing the history of clicks (|B|C|) and the concatenated value for the next potential click. We can keep building the parameter string based on clicks until another parameter action clears the string or manipulates the value in some other way. And that gives us a foundation for this example:
Dynamically drawing flight paths with parameter actions and geospatial functions
- Parameter – Flight Path Data: this is the parameter that will contain the ever growing string of data
- Parameter Write – Flight Path Data: this is the calculated field that appends every marks data to the existing parameter value
- The Parameter Action – which takes the value of Parameter Write – Flight Path Data for the mark clicked and stores it in Parameter – Flight Path Data
The code for the calculated field above starts with the existing parameter value and appends the data I want to store for the mark itself. That data is the unique Airport Code separated with a colon from the Latitude and Longitude (which are separated from each other with a comma) separated with another colon from the City and Country.
What that gives me is a pattern that I can leverage to extract the data I want. The pattern, for two iterations of clicks, looks like this:
|Airport Code:Lat,Lon:City, State|Airport Code:Lat,Lon:City, State
With that, I can use a wide range of string functions to extract exactly the data I want:
- MID() and FIND() can be used to locate the data associated with an airport mark (along with the subsequent data appended)
- SPLIT() can be used to separate each full set of data (with the | delimiter), and various levels within that (using the : and , delimiters)
- Alternately, I could leverage the power of Regular Expressions (though I didn’t in this example)
With the data I need, I can use some of the relatively new geospatial functions in Tableau to turn successive points into flight paths:
- MAKEPOINT() takes the latitude and longitude and transforms them into a geospatial point
- MAKELINE() take a point and the next on the itinerary and draws an arc between the two.
- DISTANCE() which is not included in the Tableau Public version as it is only available in the Tableau 2020.1 beta, allows me to easily calculate the distance between two points and to specify the unit of measurement.
And here is the result:
Really nice example. Thanks for posting.
My first thought when I saw it was – “why not use set actions instead?”. As that could avoid the time consuming string searches and manipulation for better performance. But then as soon as I asked the question, the answer jumped out as obvious. Order matters in this use case.
Sets don’t have a defined ordering, but parameters allow you to define an ordered list – or any other data structure you can represent in a text string form.
So maybe that’s the test for choosing which approach to take. If you have an unordered collection of data to store, try using a set first, otherwise, serialize the data structure of your choice into a string valued parameter.