PowerPoint Presentation

Creating Tableau Dashboards with

IPEDS Data: How To, Tips, and Tricks

2016 Association for Institutional Research Annual Forum

Sean V. Hoffman, Institutional Research Analyst

Office of Institutional Research, Planning & Effectiveness

Stony Brook University

Goals

• To learn what data are available in the IPEDS data center, and how to

retrieve them.

• To be able to use these data to create a basic benchmarking dashboard

with Tableau.

• To review some advanced techniques for creating more useful and

informative visualizations in Tableau.

• To gain an understanding of how these dashboards can be used to aid in

institutional research and planning.

GOALS

AGENDA

Agenda

Tableau basics

The IPEDS Data Center

Building a dashboard – Admissions Data

• Basic – Filters and calculated fields

• Intermediate – Parameters and more calculated fields

Advanced techniques

• Comparison charts

• Chart switching on a dashboard

• Dashboard actions

Uses for Institutional Research

Questions

TABLEAU BASICS

Tableau Desktop

• Create, manage, edit, and update workbooks locally. Can be deployed to a server

(campus, public or online).

• Personal vs Professional – Need professional for DB connections

Tableau Server

• View and edit published workbooks in a secure environment

Tableau Online

Tableau Public

• Free, but not secure

Tableau Reader

• View and interact with dashboards

Backwards compatibility issues

IPEDS DATA CENTER: WHAT’S AVAILABLE

Find Your College

-College navigator

-College scorecard

Use the Data

-IPEDS Data Center

-Customizable,

downloadable data

Report Your Data

-Log in to complete

surveys

-Collection Level Data

Center (Most recent data

available)

Join In

-Training

-Resources

All data reported to IPEDS is available, as well as copies of

the actual surveys submitted

IPEDS DATA CENTER: WHAT’S AVAILABLE

• For this exercise we

will use “Compare

Institutions”

IPEDS DATA CENTER: WHAT’S AVAILABLE

• Data from all surveys are available (final or provisional)

• Collection level data center allows access to current year (Go back to main page 

Click Report Your Data  “Answer The Current Survey”  Log In  Click “Tools” 

Click “Go to Collection Level Data Center)

• Select institutions: can create groups, use groups, or download all (By groups  EZ

Group  “All Institutions”)

IPEDS DATA CENTER: EXPORTING DATA

-Select the variables you would

like (up to 250 for “Compare

Institutions”)

-Click continue

-Download a CSV file

-You can create derived

variables if you desire

IPEDS DATA CENTER: EXPORTING DATA

-Each element is downloaded as a separate column. This file can be quickly chopped up to

work well in Tableau.

-For this example, we can stack the panel based on year and gender, leaving only 8

columns. This will allow for a better layout in Tableau.

CSV from IPEDS Stacked Panel

BASIC DASHBOARD: IMPORTING DATA

Connect to a file 

-You can edit the file and refresh

once you’ve started.

Connect to a server 

-Need Tableau Desktop

Professional

-Can create an extract

BASIC DASHBOARD

• Let’s begin with:

– Filters

» Filters are based off of certain fields (columns)

» Because of the way we stacked the example panel, we can

filter, gender, year and school

» To create, drag the item to the filter shelf. To show, right click

and choose “Show Quick Filter”

– Calculated fields

» Calculated fields are similar to excel formulas

» We can create fields to calculate admit rate and yield

» To create, right click in the data tab and select “Create

Calculated Field”

• Now let’s create two charts based on the items we

have:

– A column chart to display Applications, Admissions and

Enrollments

– A table to display Admit rate and yield

BASIC DASHBOARD

• Now, we can create a

new dashboard and add

the two charts to it

• Drag the sheets from

the dashboard shelf to

the dashboard page

• All charts and filters

from the sheets will be

brought to the

dashboard

• We can select each

object, and use the

dropdown menu to

choose to make the

object floating (free

placement)

SOME REFERENCE NOTES

Notes about filters:

1) A filter can be set to apply to a sheet, or all sheets using

the same source. This allows for you to only need one filter

with multiple charts

2) A filter can be set to only show relevant values. This is

helpful with long lists of related items. Example:

Colleges/Schools and Majors

3) Filters can be set to include an “All” option. In this

example, it has been deactivated, as the data set already

has an all option

All options can be accessed from the drop down menu

on the quick filter

Notes about the charts:

1) To add multiple measures to the measure shelf, start by

adding two measures to the row shelf. Then drag one from

the row shelf into the axis of the other. This will bring the

measures shelf up, and allow you to add more measures

directly to it.

INTERMEDIATE DASHBOARD

• Here is another step to add some functionality to our dashboard. We will use a

parameter and calculated field to select one metric to display at a time.

– Parameter

» Parameters allow you to create more complex filters

» Let’s create a parameter to choose what specific metric we want to look at in a chart

» Create a parameter that allows you to choose from the strings: “Applications”, “Admissions”,

“Enrollments”, “Admit Rate” or “Yield Rate”

» To create, right click in the data tab and select “Create Parameter”

– Calculated field

» We can now create a calculated field that uses the

parameter to select the item. For example, this field

will show Admissions if parameter selected is Admissions.

» This will be a multiple IF statement. In Tableau you must

end an IF statement with “END”

• Now let’s update our dashboard!

INTERMEDIATE DASHBOARD

• Observe that we

do not need to

store multiple

metrics in multiple

tables (or one

table with multiple

columns)

• This can help

when space is

limited

• This allows for

easy

benchmarking on

one metric

LIMITATIONS

Limitations of the dashboard

1. Benchmarking: What if we want to focus on our institution, or another

selected institution?

2. Data types: Our graphs and labels will not work for multiple data types

3. Size and content: How much is too much for one dashboard?

Advanced techniques to address the limitations

1. Comparison Charts – Create a benchmarking chart

2. Chart Switching – Select a chart based on the data you wish to display

3. Dashboard Actions – Switch between dashboards (or other links) and create

filters that pass between charts

ADVANCED: COMPARISON CHART

Goal – Highlight a selected institution and

compare to the average (quartiles also

possible)

Solution – Create a comparison chart

• Step 1 – Add a parameter using the

institution names field

• Step 2 – Create a calculated field that sets

its value based on the parameter. For

example, create a parameter called:

“Selected Institution Applications”

• Step 3 – Set this field to be equal to the

number of applications when the Institution

Name field is equal to the parameter, and

null otherwise. This will make sure this field

only displays data for the institution you are

interested in

Step 1

Step

2/3

ADVANCED: COMPARISON CHART

Let’s examine how to merge this with the chart we created in the intermediate dashboard

• In that chart, we created a calculated field to display our metric of choice.

• Now, we will create another calculated field to display our metric of choice for our focus institution.

For example, call the calculated field: “Selected Institution Metric”

• We can then add both metrics to a chart, and create a dual axis chart.

• To merge both axis, ensure both calculated fields are the same data type, by adding the float function

to your calculated field: ( FLOAT(calculation) ) Not applicable for this data set.

ADVANCED: COMPARISON CHARTS

• Gray = Average

selected measure for

all institutions – this

is determined by the

metric parameter

• Red = Selected

measure for our

focus institution –

determined by the

metric and focus

institution

parameters

COMPARISON CHARTS

Alternate Comparison Chart

Highlighting the selected institution

• Create a calculated field called

“Is Selected”. This will be

binary ( 1 or 0 )

• Drag the field over to the

color shelf, change it to

discrete, and assign a

highlight color to 1 and

standard color to 0.

ADVANCED: CHART SWITCHING

Goal – Display all metrics in one chart area

• This is made challenging by different data types, and different trend representations (we would like

to trend admit rate along a reversed axis and yield along a regular axis).

Solution – Place multiple charts in the same location on the dashboard, and display only one at a time

• This is will be done through the use of a parameter, a calculated field, and custom list filters.

• For this dashboard, there will be three charts

1. Admissions, Applications, and Enrollments displayed as numbers, trended along a regular axis

2. Admit Rate displayed as a percentage, trended along a reversed axis

3. Yield displayed as a percentage, trended along a normal axis

ADVANCED: CHART SWITCHING

Step 1 – Create all charts needed for the dashboard

• Three, as mentioned previously

Step 2 – We will need a parameter that has the values we want to switch between

• Use the parameter created in the intermediate dashboard

Step 3 – Create a calculated field that is equal to the parameter

• This will help to switch between charts

ADVANCED: CHART SWITCHING

Step 4 – On each chart, add the calculated field as a filter,

using a custom list. Set the custom list to the appropriate

metrics for the chart

• For example, the Admit Rate chart should have the

calculated field filtered only on “Admit Rate”. When the

parameter is switched to “Admit Rate”, it will trigger the

calculated field filter and display any chart with “Admit

Rate” filtered

Step 5 – Apply filters to all charts, and stack them on the

dashboard

• As long as each value in the parameter is included in

only ONE of the stacked charts, then only one chart at

a time will be visible

Note: if chart titles are different, hide the titles, and use a

separate sheet to create a universal title

To stack, set all charts to “floating” and place them at

the same coordinate location, using the manual

coordinate entry section of the dashboard (bottom left)

Filter for the Applications,

Admissions, and Enrollments chart.

The Admit Rate and Yield charts

will only have one element each.

ADVANCED: DASHBOARD ACTIONS

Dashboard Actions

• Actions allow for dynamic interaction between objects on dashboards

• These actions can be used to communicate between sheets, dashboards,

and external objects as well

• To add a dashboard action go to “Dashboard”  “Actions”  “Add Action”

• All actions offer the choice to run on: Hover, Select, or Menu

• Hover – When the mouse hovers over the target, the action will trigger

• Select – When the target is selected, the action will trigger (the target will have to be unselected

and then selected again to re-trigger)

• Menu – When the target is selected, a menu will display, giving you the option to trigger the action

ADVANCED: DASHBOARD ACTIONS

Dashboard Action – Filter

For passing filters between sheets

• Choose a source and a target. The source is the object being clicked, and

the target is what will be filtered.

• For example, if there is a table showing all institutions, we can use a filter

action to display institution detail when the name is clicked.

• If the target sheet is on another dashboard, this will switch to that dashboard.

For switching between dashboards

• Using the above knowledge, we can use the filter actions to navigate

between sheets without passing filters as well. Simply choose all items as

targets on the dashboard you wish to navigate to

• Instead of using a chart as a source, a navigation button can be created by

adding a calculated field with the value of “image”, and setting the mark type

to Shape (see example Tableau workbook)

ADVANCED: DASHBOARD ACTIONS

Dashboard Action – Filter

• “Show all values” will pass the

filter when selected, but remove it

when the source is unselected

(Exclude all will pass the filter as

an exclusion filter)

• “Leave the filter” will leave the

filter until a different element of

the source is selected

• This filter will show the detail for

institution name that is clicked on

the “Detail All Inst” chart (Source).

It will be displayed on the “Detail

by Year” chart (Target).

ADVANCED: DASHBOARD ACTIONS

Dashboard Action – URL

• URL actions all for navigation to external addresses, such as a webpage or a

file on a file share

• When adding the action, choose the target on the dashboard that will activate

the action, then input the URL.

Dashboard Action – Highlight

• Highlight actions allow for focusing on subsets of the dashboards. You can

pass the action between sheets to filter a graph based on something clicked

in a table

• We will not add a highlight action to this visualization, but their uses are well

documented in the Tableau forums

Planning

• Examining position

• Trends and projections

Research

• Highlights areas of interest

Benefits

• Quick, relatively simple, and publicly available

• Well populated

• Customizable peer groups

Going further

• Adding institutional data (dashboard example on next slides)

USES FOR INSTITUTIONAL RESEARCH

Questions or comments?

Feel free to contact me:

Sean Hoffman

Stony Brook University

Office of Institutional Research, Planning & Effectiveness

sean.hof[email protected]du

(631)632-1462

http://www.stonybrook.edu/commcms/irpe/index.html (Main IRPE site)

http://www.stonybrook.edu/commcms/irpe/reports/ (Presentations)

http://paintbynumbersblog.blogspot.com/2014/10/a-rough-guide-to-tableau-

dashboard.html (Dashboard Actions)

https://community.tableau.com/community/forums (Tableau Community Forums)