US College Tuition


Facebooktwittergoogle_pluspinterestlinkedinmailFacebooktwittergoogle_pluspinterestlinkedinmail

tuitionFor #MakeoverMonday on Monday 2nd May 2016 the challenge was to build a Tableau Dashboard based on US College Tuition. I do like Tableau’s mapping functionality but saw several examples of Tableau Hexagon Maps and thought I would give it a try.

Before you start I would recommend checking out the Dashboard on Tableau Public by clicking HERE

The Data Sets

The data set for this dashboard is available on Andy Kriebel’s blog as a Tableau Data Extension (TDE) or an Excel document. Let us start by clicking HERE to download the Tableau Data Extension (TDE).

Once the Tableau Data Extension is downloaded double click on it to explore this within Tableau. Tableau Desktop should open and show you the following:

tuition01

We will also need a Data Source for the Hexagon Layout Structure. Click HERE to download the CSV file. Once downloaded add this to Tableau:

  • In Menu Bar select Data
  • Select New Data Source
  • Choose Text File and select the Hexagon-Map.csv

You should get a new Data Source which will look like the following:

tuition02

Now that we have our two Data Sources we are now going to import our Customer shape.

Custom Shape

To import the Custom Shape required for this dashboard we will start by saving the the following image:

hexagon

Click HERE to get the image.

Once saved:

  • Go to C:\Users\<Your Name>\Documents\My Tableau Repository\Shapes
  • Create a new folder called US College Tuition
  • Copy the hexagon.png to this new folder

This image will now be available in Tableau Desktop and we are ready to go.

The Hexagon Map

The first sheet we will build is the US Hexagon Map:

  • Rename your first Worksheet to The Map
  • In the Average Tuition in the United States data source drag State into the Filters Panel.
  • Check the Exclude box
  • Select Puerto Rico and U.S. Avg
  • Click OK
  • Right click of the State pill in the Filters
    • Go to Apply to worksheets
    • Select All using this data source
  • In the Hexagon Map data source click on the Link Icon next to the State dimension.
    • This will ensure that the Data Sources are linked.
  • Drag the Columns measure onto the Columns
  • Drag the Rows measure onto the Rows
  • Using the right click menu change both objects to Dimension

You should have something that resembles the following

tuition03

We will now format this Worksheet to make it resemble the US Map and use the Custom Shape we imported:

  • Double click on the Rows header on the Y Axis
  • Make sure you check the Reversed check box to invert the Axis
  • Click Apply which will flip the y-axis
  • Right click on the Row pill and deselect Show Header
  • Right click on the Column pill and deselect Show Header
  • Right click on the Visualisation and click Format
  • Click on Format Shading
    • Select the Worksheet color to be a light gray
    • We will use this light gray as the background color for all our worksheets
  • Click on Format Borders
    • In Row Divider, Pane select None
    • In Column Divider, Pane select None
  • Click on Format Lines
    • in Grid Lines select None
    • in Zero Lines select None

You should now have a visualisation that resembles the following

tuition04

Now we will make sure of our Custom Shape and also add the State abbreviation:

  • Click on the Shape Mark and select More Shapes
    • Click on the Reload Shapes button to ensure that your Custom Shape has been loaded.
  • In Select Shape Palette choose US College Tuition
  • Click on the Circle in the Select Data Item and choose the Hexagon
  • Click Apply
  • Click on the Size Mark and adjust the size to the 75% of maximum.
  • In the Hexagon Map data source drag the Abbreviation into the Label Mark
    • Click on the Label Mark
    • In Alignment, for both the Vertical and Horizontal select the Center
    • Also select Allow labels to overlap other marks

We should now have a visualisation which looks like the following

tuition05

For the last step we are now going to add some color to our visualisation:

  • In the Average Tuition in the United States data source drag Tuition & Fees onto the Color Mark
    • Click on the Color Mark and choose Edit Color
    • Under Palette select Red-Green Diverging
    • In Stepped Color select 10
    • Check the Reversed check box
    • Click on Advanced
    • Click on the Start box and enter 3,000
    • Click on the End box and enter 15,000
    • Click on the Center box and enter 9,000

You should see the following

tuition06

Click OK.

  • In the Average Tuition in the United States data source drag State onto the Tooltip Mark
  • Click on the Tooltip
    • Deselect the Include command buttons
    • Remove all text apart from <(State)>
    • Click OK

You should now see the following visualisation

tuition07

Please Save your workbook.

NOTE: Do not worry that it is all red at this moment. This is because our Color range has been fixed for individual years but currently the map takes all years into account. This will be fixed later with Dashboard actions.

Academic Year (Selection Worksheet)

We will create a worksheet that which will allow us to select the Academic Year of interest.

  • Create a New Worksheet
  • Rename this Academic Year Selector
  • In the Average Tuition in the United States drag the Academic Year Dimension into the Columns and Text
  • Right click of the Academic Year in the Columns and deselect Show Headers
  • Right click on the visualisation and click Format
  • In Format Font change the the Default worksheet font size to 14pt
  • Click on Format Shading
    • Select the Worksheet color to be a light gray
    • We will use this light gray as the background color for all our worksheets
  • Click on Format Borders
    • In Row Divider, Pane select None
    • In Column Divider, Pane select None
  • Click of the Tooltip Mark
    • Deselect Show Tooltips

You should now have the following

tuition08

Please Save your workbook.

NOTE: As before do not worry about the way the worksheet looks at the moment. We will tweak all the sizes once we combine our worksheets into a Dashboard.

State Tuition Growth

We now build our line chart which will show us the growth trends of Tuition fee within a particular State.

  • Create a New Workbook
  • Rename this Tuition Growth
  • Create a New Calculated Field called Year with the following: INT(LEFT([Academic Year],4))
  • Drag this object onto the Columns
    • Make this object Continuous
    • Make this object a Dimension
  • Drag Tuition & Fees onto the Rows
  • Click on Show Me and choose the Line graph
  • Drag Tuition & Fees onto the Color Mark
    • Click on the Color Mark and choose Edit Color
    • Under Palette select Red-Green Diverging
    • In Stepped Color select 10
    • Check the Reversed check box
    • Click on Advanced
    • Click on the Start box and enter 3,000
    • Click on the End box and enter 15,000
    • Click on the Center box and enter 9,000
    • Click on Apply
  • Drag Tuition & Fees onto the Label Mark
  • Click on the Label Mark
    • Select Show mark labels
    • Select Line Ends
    • Select Allow labels to overlap other marks
    • Select Label start of line
    • Select Label end of line

You should see the following

tuition09

Now we will format this worksheet:

  • Double Click on the Y-Axis and deselect Include Zero
  • Click Ok
  • Right click on the SUM(Tuition & Fees) pill and deselect Show Header
  • Right click on the Year pill and deselect Show Header
  • Right click on the visualisation and select Format
  • Click on Format Shading
    • Select the Worksheet color to be a light gray
    • We will use this light gray as the background color for all our worksheets
  • Click on Format Borders
    • In Row Divider, Pane select None
    • In Column Divider, Pane select None
  • Click on Format Lines
    • in Grid Lines select None
    • in Zero Lines select None

You should now see the following

tuition10

Please Save your workbook.

NOTE: As before do not worry about the color at the moment or the large values.

Commentary Worksheet

For our final Worksheet we will build in some commentary.

  • Create a New Worksheet
  • Rename this Commentary
  • In the Average Tuition in the United States create three New Calculated FIelds
    • 2004-05 Tuition with IIF([Academic Year]=”2004-05″,[Tuition & Fees],0)
    • 2015-16 Tuition with IIF([Academic Year]=”2015-16″,[Tuition & Fees],0)
    • Growth with [2015-16 Tuition] – [2004-05 Tuition]
  • Drag the three Calculated fields on to the Text Mark
  • Drag State onto the Text Mark
  • From the Hexagon Map drag Abbreviation onto the Text Mark
  • Click on the Text Mark and change the Alignment to Center
  • Change the Text in the Text Mark to:

tuition11

Now we will format the visualisation:

  • Right click on the visualisation and click Format
  • Click on Format Shading
    • Select the Worksheet color to be a light gray
  • Click on Format Borders
    • In Row Divider, Pane select None
    • In Column Divider, Pane select None

You should now see the following

tuition12

Now Save your workbook.

The Dashboard

Now that we have all the Worksheets built we will now place them onto a Dashboard and add interactions:

  • Create a New Dashboard
  • Rename this US College Tuition
  • In Menu click on Dashboard and Format
  • Under Default Shading Select the default to light gray
  • Click on Floating and drag all the Worksheets onto the Dashboard in the following order
    • The Map
    • Academic Year Selector
    • Tuition Growth
    • Commentary

Rearrange your worksheets to resemble the following

tuition13

Now we will add two actions to our Dashboard:

  • In the Main Menu click on Dashboard and then Actions…
  • Click of Add Action and choose Filter…
  • In the Add Filter Action
    • Name the filter Date Selector
    • In Source Sheet uncheck all but Academic Year Selector
    • In Run action on choose Select
    • In Target Sheet select The Map
    • In Clearing the selection will select Leave the filter
    • In Target Filters select All fields

You should see the following

tuition14

Click OK to close this dialogue box and OK again to close the Actions box and test your action by click on the Academic Year Selector Worksheet.

Once tested the Academic Year Selector we will add a State Selector Action.

  • In the Main Menu click on Dashboard and then Actions…
  • Click of Add Action and choose Filter…
  • In the Add Filter Action
    • Name the filter State Selector
    • In Source Sheet uncheck all but The Map
    • In Run action on choose Select
    • In Target Sheet select Commentary and Tuition Growth
    • In Clearing the selection will select Leave the filter
    • In Target Filters select All fields

You should see the following

tuition15

Click OK to close this dialogue box and OK again to close the Actions box and test your action by click on the The Map Worksheet.

Finishing Touches

Now that we have our Dashboard built all we need to do is add some finishing tweaks to your Dashboard to ensure a professional finish.

You should be aiming to get something along the lines of the following

tuition16

This brings us to the end of our tutorial and I hope you had fun, was challenged and learned a few cool things; I do hope that you visit my blog again for more Tableau Dashboard tutorial.

If you have any comments please leave them below for me and if you found this tutorial useful please share it.

Credit goes to Matt Chambers (@SirVizAlot) for his tutorial on Hex Tile Maps HERE

Facebooktwittergoogle_pluspinterestlinkedinmailFacebooktwittergoogle_pluspinterestlinkedinmail

Toan Hoang

About Toan Hoang

Toan Hoang is a technology enthusiast with a broad understanding and appreciation of most aspects of technology but with a passion for Business Intelligence Solutions and Technologies, Data Management and Web Programming.