Warning: Forgive me if I don't use the right terms - I am very new to tableau.
I am working on a project to focusing on population of people living within a selected dynamic radius distance (in km). Second part of the project is to show which of provincial road networks pass through the selected circle.
What I have:
1. Shape file of 2016 population census of the province (Saskatchewan, Canada) - down to dissemination block area units IDs (DBAUIDs). This shape file also has areas (in square kms) of all the DBAUID's.
2. Shape Map of the provincial road networks - different classes of roads, town names, metro centers, etc.
My main focus is to draw circles (dynamic radius) around top 9 city centers by population. And show the population living within that radius AND amount spent on maintaining road networks passing through that circle - by increasing or decreasing the radius. I found a very good example here (Richard Leeke's Super-Charged ZIP Code Radius-Finder ) but can't reproduce it as I have limited knowledge on GIS.
What I have done:
1. I was able to build circles using the lat/long of 9 cities - chosen manually (But I wish this can be automated for all cities and be able to select by filters).
2. I was also able to change the circle radius using a parameter.
I am stuck with overlaying the population files and road network files and making them work with the circles. Like I said earlier, this (workbook found here >> Richard Leeke's Super-Charged ZIP Code Radius-Finder is exactly what I have in mind to achieve. I need help on this. Thank you for being kind enough to help me advance in Tableau. All of the definition to understand the census data are here: Table 4.12 Dissemination area boundary files record layout.
Message was edited by: Jim Dehner
I removed the list of "Mentioned" - see
OK, explanations for the last two views and the dashboard.
Highway Spending per Capita for Selected Areas
This builds on a lot of the things we did in the previous view, but has one extra twist which I'll explain first before the detailed instructions.
The table calculation for the field [Amount Spent per Capita] uses the expression:
[Selected Amount Spent] / [Selected Population]
where both of those are themselves table calculation, but with different addressing: [Selected Amount Spent] is calculated over all roadnames whereas [Selected Population] is calculated over all dissemination blocks. This means we can't just use the 'Compute Using' menu option for [Amount Spent per Capita] to set the addressing, because that only allows us to pick one field, so only one of the two component calculations would be correct. Instead we need to use the 'Edit Table Calculation' option which gives the option to set the addressing individually for each component calculation:
The dialog has a drop-down list allowing you to choose each component calculation in turn.
At first sight you would think that for 'Selected Population' you should just check [dbuid] (meaning the calculation would be performed over all [dbuid] values for each combination of [Highway Roadname] and [Population Center]) and for [Selected Amount Spent] you should check [Highway Roadname] (meaning the calculation would be performed over all [Highway Roadname] values for each combination of [dbuid] and [Population Center]). But that results in [Amount Spent per Capita] being null. This screenshot of the 'show data' window hopefully explain what is happening here: [Selected Population] is always null for highway rows and [Selected Amount Spent] is null for all dissemination block rows - so [Amount Spent per Capita] is always null (because one or other of the two fields used to calculate it is always null).
So actually what we need to do is perform both calculations over all values of both [Highway Roadname] and [dbuid] for each [Population Center], by setting both like this:
That means that both components calculations and hence the [Amount Spent per Capita] are evaluated correctly for every row, as shown here:
We will again want to filter the view down to a single row, using the [First Row?] field, as we did previously.
Step by step instructions.
1) Create a new sheet and call it 'Highway Spending per Capita for Selected Areas'.
2) Drag [Population Center] from datasource 'Pop Centre Dissemination Block Highway' onto the Rows shelf.
3) Switch to sheet 'Dissemination Blocks and Highways around Population Centers' and for both the population and population center filters select 'Apply to Worksheets' and select 'Highway Spending per Capita for Selected Areas' to apply those filters to our new sheet.
4) Switch back to 'Highway Spending per Capita for Selected Areas' and drag [dbuid] and [Highway Roadname] onto the detail shelf.
5) Drag [Amount Spent per Capita] onto the Columns shelf, and set the addressing using the 'Edit Table Calculation' option from the drop-down menu on the field. Select both [dbuid] and [Highway Roadname] for each of the two component calculated fields, as per the explanation above.
6) Drag [Is First?] onto the filter shelf and select 'True' when prompted (the reason it only offers 'True' is because with the default addressing there is only one row per partition - so that row is always the first row in its partition). Now use 'Edit Table Calculation' to set the addressing to use both [dbuid] and [Highway Roadname] as above. Finally select 'True' when offered 'True' or 'False' to filter to just the first row.
7) From the Label shelf select 'Show mark labels'.
8) Optionally format the [Amount Spent per Capita] field and axis to use currency format.
Your view should now look like this for Saskatoon.
Highway Spending by Population Center and Route
Last sheet. This one is very straightforward, no new techniques or concepts, so just brief step-by-step instructions should do.
1) Create a new sheet and call it 'Highway Spending by Population Center and Route'.
2) Drag [Population Center] from datasource 'Pop Centre Dissemination Block Highway' onto the Rows shelf.
3) Switch to sheet 'Dissemination Blocks and Highways around Population Centers' and for both the population and population center filters select 'Apply to Worksheets' and select 'Highway Spending by Population Center and Route' to apply those filters to our new sheet.
4) Switch back to 'Highway Spending by Population Center and Route', drag [Highway Roadname] onto the Detail shelf and [Highway Route Number1] onto the Rows shelf.
5) Drag [Selected Amount Spent] onto the Columns shelf, and set the addressing using the 'Compute Using' option from the drop-down menu on the field. Select [Highway Roadname].
6) Drag [Is First?] onto the filter shelf and select 'True' when prompted. Now use the 'Compute Using' menu option to set the addressing to use [Highway Roadname] as above. Finally select 'True' when offered 'True' or 'False'.
7) From the Label shelf select 'Show mark labels'.
8) Optionally format the [Selected Amount Spent] field and axis to use currency format.
Your view should now look like this for Saskatoon.
One thing that is worth pointing out is that the calculated field [Selected Amount Spent] is the same field as we used on sheet 'Population and Highway Spending for Selected Areas', but gives a different answer this time because we have the route number dimension as well as population center on this sheet, so the result is broken down further.
Finally create a dashboard, drag all the sheets on and arrange as you see fit.
The filters should all just work on all sheets because of the way that we applied the filters from the first sheet to all of the other sheets.
You will probably want to edit the titles and alias some of the entries in the legends and do various other bits of tidy up. Here's what mine looks like:
For extra bonus points you can do things like adding highlight actions to the dashboard so that you can click on route numbers in the bar chart and highlight the routes on the map:
I noticed that you had created extracts for both the census CSV file and the expenditure spreadsheet. That is definitely a good thing to do - after seeing that I've done it for mine and although I haven't timed it the workbook seems quicker - it is certainly really responsive. One thing you could do is recreate the census extract with a filter on province, so that you only include dissemination blocks for Saskatchewan. I don't think that will make a measurable difference to performance, but it saves a bit of space - and it's a good habit to get into, I think - no point in carrying around lots of excess data that is unrelated to what you are doing.
So that's all there is to it.
It has certainly turned into a much bigger exercise than I was expecting and we have covered an awful lot of ground and lots of different Tableau concepts and techniques. It's actually been really useful for me - I've been right out of the Tableau world for a long time so it's been a great chance to refresh my knowledge and get up to date on all the changes that have been happening since I was last using it regularly.
As I say, I'm fully expecting some more questions (including working through the one you are stuck on at the moment). I'll get to those as and when I'm around.
Good luck!
