When it is nearly the end of the Data School training program, I find myself learned a lot along the process. However, the more we learned, the more practical use case we should practice to apply the techniques and the Work Out Wednesday contains plenty of use cases that are very good for practicing with. Every time I take the challenge, I feel like I am solving a puzzle using my Tableau skill sets and learning something new at the same time.
In this blog, I would like to discuss my solution to the latest(Week 36) challenge in WOW. The topic is ‘Can you find all counties within N miles of a selected county?’. It involves the Distance calculation, which was published in the 2019.3 version of Tableau. I will talk through the challenge’s requirements, my thinking process to solve it, and my solution towards the challenge.
The requirements of the challenge
- Dashboard Size: 1366px by 968 px
- # of Sheets – up to you
- filter the dashboard by selected county and all counties within a user-selected radius in miles
- A bar chart showing selected counties sorted by cumulative cases as of 8/26 and colored by percentile rank of the number of hospital beds
- Create an estimated utilization column chart to show the cumulative utilization for all selected counties
- Create a map that shows the location of the counties selected ALONG WITH the state borders of applicable states
- Match formatting
- Match tooltips
- I created mine WITHOUT ANY ACTIONS…Can you?
- OPTIONAL: No dashboard OR sheet actions
My main solution to approach the challenge
After I get the challenge requirements, the first thing I would like to do is to analyze the main dimensions in the charts, which is not included in the dataset and need to create and all the techniques are involved:
A set to select the county.
A Parameter to select the distance.
The percentile rank of the number of hospital beds.
The distance measure shows the distance between the selected county and other counties.
I realized the only measure not included in is the distance measure, and it is relevant to all the charts in the dashboard. That would be the main challenge of this WOW. However, after analyzing the challenge, I decided to create a set and parameter first. These two are easy to build and will be used in the distance selection control. Below are my set and parameter:
Now, it is time to create the Distance measure. If we look at the syntax of the Distance formula: Distance(Start, End, Unit), we can see it needs a start point and an endpoint. However, the data set only contain ‘State,’ ‘County’ and ‘County, State’ area type of spatial objects and latitude and longitude for the County. That means before we can have the distance measure, we should create several intermediate dimensions first.
At first, my plan was to create a point based on the lat and long of County using the Makepoint formula, use Fixed formula to fix the selected County point to the entire window. After that, I could create a distance between Counties and the selected County. Sounds perfect. However, Tableau doesn’t allow me to do LOD base on the spatial objects. The error looks like below. And I also tried different aggregate methods, and they didn’t work either:
After I notice that, I tried another way to approach the solution. Instead of making a point first, I created the latitude and longitude for the selected County and fixed the lat and long of the selected County first:
Then I create the distance using the Distance formula:
After the distance was created, I created another set based on the ‘County, State’:
As long as I had the right calculation, I applied them to the view and put percentile of ‘Num Staffed Beds’ to the color marks for ‘Case by County’ and the ‘Map of Hospital By County.’ The dashboard looks like this:
And when I select, it looks like this.
It is still a pixel away. That is because I didn’t adjust some settings for the map. To be honest, I didn’t know how to apply the state border and make Alaska and Hawaii look smaller and visualize the USA map more condense. I learned that after I saw the solution:
Get border of the state: Using Dural Axis of ‘State’ and ‘County.’
Make Two states smaller: Split the map into 3 maps of the Mainland, Alaska Hawaii.
Now I can have the same dashboard as WOW week36, and when l select the specific County, I can have the beautiful state border on the map.
Warp-Up
Four main things I learned from working on this WOW challenge:
- Tableau doesn’t allow us to do LOD on spacial objects because we can’t aggregate them.
- We need two points to make the distance. If there is just one point in the data set, try LOD to get another point for each row.
- Dual Axis can allow you to have a beautiful border of states.
- To condense the map, we can separate it into several maps.
This is the process of how I approached the solution for WOW week36. I learned a lot through it and hope you feel the same way as I do.
