Hello everyone! 😊
My first Biztorian blog post comes along with holiday season, so I’ll combine the power of Geospatial Tableau functionalities with Parameter Actions to get the most out of our next road trip choices.
Coming from a data science background, I was wondering whether it is possible to store information in Tableau based on previous input data in an iterative way. Well, turns out this is indeed possible! All thanks to the power and flexibility of Parameter Actions.
Let’s see how 😊
To start, I downloaded a .csv file with the list of capitals and their corresponding country and continent names from here.
Tip: when you’re not sure about where to find the data you need, Google is your friend, and most of the time you will be surprised by how useful it is! There's even a Google Search engine for datasets at https://datasetsearch.research.google.com/ . By giving some key words, you'll get a long list of datasets available on many different websites. Super convenient!
For the scope of this post, I’ll limit my view to European territory: who’s up for an inter-rail over European capitals?!
In the dataset we have the Capital Longitude and Latitude fields, but I will use Tableau's automatically generated Longitude and Latitude to create the map because that's what is needed to use the Geospatial functions later on.
I'll start by creating a dual-axis map with Capital Longitude and Latitude on details and Capital Name on label in the first layer, while the second layer will be used to plot the chosen path.
The idea is to create an iterative process where the user selects certain cities and the view builds up consequently creating a path, together with the total distance in kilometers.
To do this, we will need two parameters:
- City Parameter: we will store the name of the capitals selected by the user
- City + Coordinates Parameter: here, we will store both the name of the capitals and the corresponding coordinates. This will allow us to tell Tableau which path to plot.
I created both parameters with String data type, all values allowed, and current value blank.
Now, we need to create the calculated fields that will be linked to the parameters:
The arrow symbol indicates the flow of the cities as they are selected by the user, and the ‘:’ and ‘,’ will be useful to extract the coordinates of each destination through Tableau’s string functions.
As the parameter is currently blank, the first element will be the first selected city. The fact that we are iterating over the parameter itself allows us to ‘append’ the previous values and update it at each new selection. In this way, we are saving to memory the sequence of cities in the order defined by the user. By storing our city selections in sequence, we’re able to achieve our ultimate goal of plotting a logical route. How neat is that!
Next, we will define our Origin point, first as a string and then as a point on the map using Tableau’s Geographical fields.
As you can see, string functions allow us to extract exactly the latitude and longitude (stored in our parameter) by dividing the string in parts separated by the delimiters “:” and “,”.
Now, we’ll need to define our destination (both as a string and as a geographical point as above) and extract it from the parameter:
We have everything we need to create our Route with the MAKELINE() function between our Origin and Destination point dragged into detail in the second layer of the map. Now, we’re only missing an action that connects our click input to the parameter values.
We will create one action for each of the two parameters, so that the ‘Select’ user input will update both the City Parameter (used to display the cities sequence in the dashboard title) and the City + Coordinates Parameter that is used to plot the route on the map.
Tip: To have the option of choosing the correct calculated fields for our Field Value action, we need to drag the field in the map to detail first!
Next step was to create a Destination Set based on the condition formula
CONTAINS([City Parameter], [Capital Name])
that I used on color and size on the map to differentiate the selected cities.
And voilà, our road trip calculator is ready!
In a matter of seconds, we can now try different itineraries and compare the total distances to find the most convenient route. You can find the full workbook and play around with it on my Tableau Public.
This is just an example with European capitals, can you think how much more you can do? 😊 Summer has started, so I hope you’ll enjoy this method and you’ll find many other interesting applications for it!
Special Thanks to Joshua Milligan for the inspiration & the tricks used here.