Tableau Part 2 – NEEDS AN UPDATE
Exploratory data analysis with Tableau
Introduction to Tableau
Tutorial
Dataset for the Tutorial:
global-superstore-orders-2016
getting_started_transcript
Video
http://www.tableau.com/learn/tutorials/on-demand/getting-started
Key Points
* Import data. Headers have to be right
* Strings and types in the data view
Tableau: Arkansas 2014 Crime Rates
Build a crime chart.
FBI Crime Data for 2017
1) Import spreadsheet into Tableau
2) Original Data 2017
3) Data Interpreter
--Headers become visible
--Rename State to City-Town
4) Are Words as ABC? Are Numbers as #?
5) New Worksheet
6) City-Town to rows.
7) Violent crime to columns. Check that measure is SUM
8) Add violent crime to colors.
--Click colors. Green-Red diverging. Reverse colors
9) Add violent crime to labels
10) Labels. Formatting.
--Headline: Click "Sheet 1"
11) Sort ascending
--This chart is ridiculously long!
12) Filter: 50 or more violent crimes
--Drag Violent Crime to Filter. 50 minimum.
Your end product should look like this:
Part 2:
1) Export a jpg version of your chart
--Worksheet | Export | Image
2) Engage in excessive self-congratulatory behavior.
Power Users:
Once you have done that, add a violent crime rate
1) Duplicate Sheet 1
--This copies your whole work to a new sheet
2) Calculate a Violent Crime Rate Per 1,000
--Click on Violent Crime - down arrow | Create | Calculated Field
--Name the Calculation "Violent Crime Rate Per 1,000"
--Divide Violent Crime into Population:
--Add a Division Symbol after [Violent Crime]/[Population]
--Enclose in parentheses: ([Violent Crime]/[Population])*1000
3) Drag Violent Crime Rate Per 1,000 to Columns.
--Remove Violent Crime
--Sort
4) Check your colors and labels.
--Drag Violent Crime Rate Per 1,000 to Colors
--Drag Violent Crime Rate Per 1,000 to labels
--Drag Violent Crime Rate Per 1,000 to filter
4) Headline, Format Legends, Export jpg
Tableau Ninjas:
Once you have done that, chart a stacked bar chart with violent and property crime.
Tableau Calculations
Calculations sample workbook
Calculations video
Introduction to Calculations Transcript
Table Calculation: Add a new column that adds
Table Calculation: Add a new column divides graduate debt into enrollment
–In Measures Data Tab, Left Click on GRAD_DEBT_MDN, Create Calculated Field
–Put in this formula: sum[GRAD_DEBT_MDN]/[UGDS]
–Rename This as: Grad Debt Per Capita Enrollment
Drag INSTNM to Rows
#Grad Debt Per Capita Enrollment to Columns
Format with a Title, add appropriate annotations.
Color the graphic, include labels.
Showing Context – Total In Tableau
https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_totals_grandtotal_turnon.htm#Agg
DL-by-Delinquency-Location-2gms0o8
Tableau to Visualize Census Data
Here’s a start:
–Download and clean the data: US Ark Counties Poverty ACS_16_5YR_DP03_with_ann-1w6iwss
We have a lot of data in this spreadsheet. Let’s simplify things. We will visualize just one column- Column J –:
“Percent; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – $15,000 to $24,999”
–Copy the “Edited Poverty” tab and rename it Min Wage Only
–Delete all of the columns except for the Percent; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – $15,000 to $24,999″
–Fix the column headings so it plays nice with Tableau. Delete the Row 1 “GEO.display-label…”
–Simplify your new column B: % Households – $15k-$25k
–Save, backup etc and open Tableau.
–Import your data
–Open new tab
–Geography to Columns. % Households $15k- $25k to rows
–Sort by largest-smallest
–Filter by Geography – drag Geography to Filter. Filter to keep the following:
Arkansas, Benton,Carroll, Crawford, Madison, Sebastian, United States, Washington
Formatting. This is the standard formatting you will apply to all of your charts in some fashion. We are simplifying and clearly marking the labels
–Drag to Label in Marks box: % Households $15k- $25k to rows
–Format Label to % – Format | Pane | Default | Numbers | Numbers: Custom | 1 decimal. % as suffix.
–Format Y Axis to %. – Format | Axis | Scale | Numbers | Number: Custom | no decimal, % as suffix.
–Color the bars. Drag to colors in Marks box: % Households $15k- $25k to rows. Edit colors to blue – red scale, reverse colors so red = highest.
–Label graph: Double Click on “Sheet 1” above the chart. Text box emerges with <Sheet Name> Type in name of chart: Minimum Wage Households Percentage of Households Earning $15,000-$24,999 in 2016 Source: U.S. Census
–Fix X axis labels (squished) . Drag the right chart out to expand the visualization.
Export image
–Worksheet | Export | Image
–Click off color legend option
–Title Minimum Wage Arkasas. Save as .jpeg
Dual Axis
Tableau Dual Axis chart
http://kb.tableau.com/articles/howto/dual-axis-bar-chart-multiple-measures
Institutions to Rows
Graduate Debt to Columns
Withdrawal Debt to Columns
Dual Axis Chart for the two debts
Display in Side by Side Bar Chart.
Edit Chart Title
Edit Labels – left click, edit alias
Rotate School Labels – left click, rotate label
Edit Y Axis Legend – left click, format. Scale. Numbers, Custom. No decimals
Part 2:
Compare single mothers in poverty to total poverty rates
Rows % Female Households and %Poverty All families
Click on Green Pill Sum (% Poverty All Families) Dual Axis
Unified Axis.
Left click on legend, synchronise axis
Marks Card – turn all poverty to bars.
Label individual cards
Tableau Dual Axis chart
http://kb.tableau.com/articles/howto/dual-axis-bar-chart-multiple-measures
Assessment:
Build a chart with a new calculated field dividing graduate debt into enrollment
Visualize by school
Format the X and Y axes, Label the rows with specific debt to enrollment (UofA is $xxxx),
Provide a headline and source.
2. Build a chart with a new calculated field dividing dropout debt into enrollment
Visualize by school
Format the X and Y axes, Label the rows with specific debt to enrollment (UofA is $xxxx),
Provide a headline and source.
3. Build a Dual Axis Chart the Calculated Fields, Graduate Debt / Enrollment and Dropout Debt / Enrollment
Filter by Beauty Schools
Visualize by school
Format the X and Y axes, Label the rows with specific debt to enrollment (UofA is $xxxx),
Provide a headline and source.
Export and upload the three image files and load to Blackboard.
Write two story ideas to come from this data work in a Word file.
Clean Zip Codes In Excel
–Copy data into new sheet: Call it “Fixed Zip”
–Insert dashes into the errant zip codes
–Save and import Fixed Zip
New sheet, begin map: double click on zip codes
Problem #2: Associate Map Data to State in Tableau
Tableau – Go to: Map | Edit Locations.
A bunch zip codes in red. Not displaying.
Further edit the zip codes. It doesn’t like the mix of short and long zip code.
Fix:
Tableau. Data. Zip Code. Split.
–New Column has only five-digit zips. Call it Zip1 – delete the other. It keeps the original. Nice.
Dual Mapping
Using ARDebt9-17
New sheet, begin map: double click on ZIP
Marks Card | Map
Drag White students to Color box, Convert to Average.
Click on Longitude pill in Columns. Press Command. Drag to Right. Release mouse
–Creates two Longitude pills and two maps
–Marks Card Now Has Controls for Two Maps
Marks Card Has Two Maps.
Lower Map, Drag UGDS Black to color. Marks Card, switch to Circle. Change Color to Orange.
Upper Map, Drag UGDS White to color, Change Color scheme to Green
In Columns, Select Down Arrow on Longitude | Dual Axis
–Maps are combined
Drag City to Marks Card | Tooltip
Edit Tooltips so data displays properly
–Select Worksheet > Tooltip. Rename the items so they are in English!
–UGDS_BLACK = Black Enrollment.
–Format | Pane | Fields AVG(UGDS _ Black) | Default | Numbers | Percentage to one decimal
Class Assignment and Homework:
Dual Maps:
– First Gen, Non-first Gen Debt all schools: Ramirez, Hennigan, Fracchia
– Male, Female Median Debt: Baird, Bonner, Zimmardi
– Debt of Students Who Withdrew, and Graduated: Ross, Tillson
– Low-Income, High-Income Students: Ellington, Neubaum, Duby
– Trend in Debt, 2015-16 vs 2016-17: Borgognoni and Thompson
– Debt At Private Vs Public Schools: Volunteer
Tutorial
https://onlinehelp.tableau.com/current/pro/desktop/en-us/maps_dualaxis.html
Embed Tableau in WordPress
–Post on WordPress.
Create a new post
Use Divi Builder, the Purple box above the formatting bar
Insert columns, pick a full row
Insert module, pick </> Code
Paste your Tableau Public embed code in the Content box
Scroll down, change the Admin Label to My Mind-Blowing Tableau Graphic (or something more humble). Save and Exit
Change Page Layout (upper right corner) to Fullwidth. Publish
Revel in your nerd powers
Building a Dashboard
Building a Dashboard
Transcript for building a Tableau dashboard
Bins and Groups
Bins and Groups
Create Bins: https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_bins.htm
Other options besides bins:
Use parameters to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/parameters_create.htm
Use sets to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm#Use
Format Filters in Tableau
https://wordpressua.uark.edu/datareporting/files/2019/08/Format-Filters-in-Tabluea.mp4
Interactive Graphics
Tableau Interactive Graphics
Dashboards with Poverty Data.
Sizing and Formatting. Navigation from Dashboard to Actual Graphic.
Simplicity in Design for Dashboard,
Space for Central Element.
Filters: Basic Filters and Filter as Sheet
Basic Filter: Applies to One Worksheet
–Click upper righthand menu on graphic, see Filters, select the appropriate data source. Here it would be total incomes under $25,000.
–Filter is created and placed at the bottom of graphic. You can format and move the filter.
–Drag by tab in upper center of filter object. Move it to the top of the filtered graphic.
“Super Filter”: Add a Filter to Control All Cities on Worksheet
Filters on Dashboards
When we click through a filter, we see that by default, it only applies to the view it came
out with. We can make this filter apply to the entire dashboard.
–Click on the filter (down triangle icon), open the menu.
–Apply to Worksheets, select “all related data sources”
Now when we make a selection, it applies to the whole dashboard
Similarly, we can make a view act as a filter for the dashboard. Click on the view to bring
up the header bar, and click the filter icon. Now if we select a header such as Second
Class, all the relevant views are filtered.
Exercise: Format Workbook for A Phone
A majority of our readers will view these stories on their phones. For the Fall 2017 Veterans Health Care project, some 70 percent of the readership was on a mobile device. Schleuss said the Los Angeles Times receives about 55 percent of its traffic on mobile devices.
Let’s format this workbook for mobile. The design choices will be radically different.
–Duplicate your workbook
–Design it with the mobile template on the left: See Upper Left Tab: Dashboard: Device Preview. Switch to Phone
–Before doing any radical surgery to your graphics, consider duplicating them and bringing in the new versions into the mobile template.
Tableau Expert Megan Putney
Megan Putney, head of Northwest Arkansas Tableau Users Group and an executive at Mikes Hard Lemonade
https://www.linkedin.com/in/megan-putney-21432839/
https://community.tableau.com/groups/northwest-arkansas
Here is some detailed guidance on your final assignment graphics and dashboards. I think these are good ideas. But if you have something better, let me know and edit this Google Doc. And we will discuss.
We will review some of Megan’s super Tableau tricks in class and then you will use a chunk of the remaining time to hammer out these dashboards and ask questions.
Create Bins: https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_bins.htm
Format Filters – See video below
Use parameters to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/parameters_create.htm
Use sets to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm#Use
Here is some detailed guidance on your final assignment graphics and dashboards. I think these are good ideas. But if you have something better, let me know and edit this Google Doc. And we will discuss.
We will review some of Megan’s super Tableau tricks in class and then you will use a chunk of the remaining time to hammer out these dashboards and ask questions.
Create Bins: https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_bins.htm
Format Filters – See video below
Use parameters to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/parameters_create.htm
Use sets to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm#Use
Dashboard Design Concepts
DashBoard Design
Version 1
Created by ravindra kumar on May 10, 2013 10:35 AM. Last modified by ravindra kumar on May 10, 2013 10:41 AM.
Role :- 1)Strategic 2)Analytical 3)Operational
1.Dashboards for strategic purposes
The primary use of dashboards today is for strategic purposes. The popular “executive dashboard,” and
most of the dashboards that support managers at any level in an organization, are strategic in nature. They
provide the quick overview that decision makers need to monitor the health and opportunities of the
business.
Dashboards of this type focus on high‐level measures of performance, including forecasts to light
the path into the future. Although these measures can benefit from contextual information to clarify the
meaning, such as comparisons to targets and brief histories, along with simple evaluators of performance
(for example, good and bad), too much information of this type or too many subtle gradations can distract
from the primary and immediate goals of the strategic decision maker.
2. Dashboards for analytical purposes
Dashboards that support data analysis require a different design approach. In these cases the information
often demands greater context, such as rich comparisons, more extensive history, and subtler performance
evaluators. Like strategic dashboards, analytical dashboards also benefit from static snapshots of data that
are not constantly changing from one moment to the next. However, more sophisticated display media are
often useful for the analyst who must examine complex data and relationships and is willing to invest the
time needed to learn how they work. Analytical dashboards should support interactions with the data, such
as drilling down into the underlying details, to enable the exploration needed to make sense of itthat is, not
just to see what is going on but to examine the causes. For example, it isn’t enough to see that sales are
decreasing; when your purpose is analysis, you must be made aware of such patterns so that you can then
explore them to discover what is causing the decrease and how it might be corrected. The dashboard itself,
as a monitoring device that tells the analyst what to investigate, need not support all the subsequent
interactions directly, but it should link as seamlessly as possible to the means to analyze the data.
3.Dashboards for operational purposes
When dashboards are used to monitor operations, they must be designed differently from those that
support strategic decision making or data analysis. The characteristic of operations that uniquely influences
the design of dashboards most is their dynamic and immediate nature. When you monitor operations, you
must maintain awareness of activities and events that are constantly changing and might require attention
and response at a moment’s notice. If the robotic arm on the manufacturing assembly line that attaches
the car door to the chassis runs out of bolts, you can’t wait until the next day to become aware of the
problem and take action. Likewise, if traffic on your web site suddenly drops to half its normal level, you
want to be notified immediately.
As with strategic dashboards, the display media on operational dashboards must be very simple. In the
stressful event of an emergency that requires an immediate response, the meaning of the situation and the
appropriate responses must be extremely clear and simple, or mistakes will be made. In contrast to
strategic dashboards, operational dashboards must have the means to grab your attention immediately if
an operation falls outside the acceptable threshold of performance. Also, the information that appears on
operational dashboards is often more specific, providing a deeper level of detail. If a critical shipment is at
risk of missing its deadline, a high‐level statistic won’t do; you need to know the order number, who’s
handling it, and where it is in the warehouse. Details like these might appear automatically on an
operational dashboard, or they might be accessed by drilling down on or hovering the mouse over higher‐
level data, so interactivity is often useful.
The ways that dashboard design must take different forms in response to different roles are clearly worth
your attention. We’ll examine some of these differences in more detail in Chapter 8, Putting It All Together,
when we review several examples of what works and what doesn’t for various purposes.
https://public.tableau.com/profile/ravi2917#!/vizhome/LorealDemo/StrategicDashboard
Format Numbers
Format the numbers once in the Measures:
Right Click – Default Properties | Number Format | Aggregation | Median
Right Click – Default Properties | Number Format | Aggregation | Percentage (or Currency …etc)
Build a graph with two measures, one by bar and the other by line.
Two measures. One Debt, one Default. Switch one to line
—Drag 3-year default rate to sheet
—Drag College Name to columns
—Drag Median Debt to far right hand column
—You have dots on view. Change median debt from Automatic to a line in Marks Card
—Change 3-year default rate from Automatic to bars in Marks Card
Format Legend Titles Correctly – Drag Legends to Bottom, Resize and Rename so words fit correctly
Relational Databases: How They Work Link on Common Fields Inner, Left, Right joins http://www.tableau.com/learn/tutorials/on-demand/join-types-union Joining the 2015-16 data to the 2016-17 data 1) Open Tableau Workbook ARDebt9-17.twbx 2) New Sheet 3) Data | New Data Source 4) Import ARDebt2015-16.xlsx 5) Add New 6) Import ARDebt9-17 7) Inner Join on Unitid 8) Rename as AR2015-17MERGED 9) New sheet, CONVERT TO measures. CAREFUL! DO THIS ONE AT A TIME. CONVERT 2015-16, THEN CONVERT 2016-17 You now have two sets of measures. One for 2015-16 and one for 2016-17 Build a chart contrasting Grad Debt Mdn changes 1) from 2016-17, Instm to Rows 2) Find Grad_Debt_Mdn for 2015-16. First Rename it GRADDEBT15-16. Then drag to columns 3) Find Grad_Debt_Mdn for 2016-17. Rename it GRADDEBT16-17. Drag to columns. 4) Sort by GRADDEBT16-17. Which ones increased? Build a chart with calculated field Grad Debt Mdn changes subtract GRADDEBT16-17 from GRADDEBT15-16 Tableau Can Be Dangerous, Part 1 Inner Joins. Outer Joins. --The evils of the inner-join and how it can mess up your math. Relational Databases: How They Work Link on Common Fields Inner, Left, Right joins http://www.tableau.com/learn/tutorials/on-demand/join-types-union https://help.tableau.com/current/pro/desktop/en-us/joining_tables.htm Exercise on Inner Joins. Spreadsheet at this link 1) Do the four corners test. How Many in Violent 2013? In Violent 2017? 2) Load data into Tableau 3) Inner join Violent 2013 and Violent 2017 on zip code 4) Build bar graph that visualizes 2017 Violent crime by City. 6) From Analytics, drag total and average line to view 7) Display the totals with labels Question: Totals and Average for Inner Join? Totals and Average for Right Join? Why are we getting different numbers?
Grouping: --Group on sheet for specific view --Grouping in dimensions pane is problematic Exercise: Show the average graduate debt for HBCUs vs Public, Private Non-Profit, Private For-Profit 1) Build basic schools and Grad Debt Mdn graphic. Set to Average. Add Control to Filter 2) Group HBCUs 3) Filter Public schools, group them 4) Unfilter 5) Filter Private non profits, group them 6) Unfilter 7) Filter Private for profits, group them 8) UnfilterSolution to Exporting Low Quality Images from Tableau 1) Worksheet | Copy | Image 2) Select Title & View 3) Open Preview 4) File | New From Clipboard --Your Tableau graphic has been pasted into Preview. 5) Export. Change Format to JPEG. Increase Quality to Best. Option: Boost Resolution to 1000 pixels/inch
Bins and Groups Bins and Groups Create Bins: https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_bins.htm Other options besides bins: Use parameters to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/parameters_create.htm Use sets to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm#Use
Assignment Create graphics. Propose a story idea. Each student interviews one person for the assignment. Refresher on how to create calculated fields: https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_formulas.htm Each person produces one graphic. The graphics should be different. Tip: Use the filtering to create different versions of the topic, i.e. top 10 or bottom 10. Jour 5283: Each student produces three separate graphics. Each team submits a 400-word draft story. Spell out the individual team members' contributions at the bottom of the story, ie, Wells interviewed Trump for the story, Jordan interviewed Bush. Memo and graphics due 11:59 p.m. Friday, Sept. 27 Another Assignment Create the following charts in Tableau: 2. Top 10 schools with the most low income debtors and high income debtors. Create jpg, upload to Blackboard 3. Top 10 schools with highest median debt for females, highest for males. Create jpg, upload to Blackboard 4. Write 250 words with your analysis of this data and propose one story idea. Answers due 11:59 pm Sept. 21 on Blackboard Another Assignment on Race Create Tableau chart, top 10 schools by student race/ethnicity: Chart 1: White schools Chart 2: African-American schools Chart 3: Hispanic schools Chart 4: White, black, hispanic, asian --Columns SUM(Ugds White) SUM(Ugds Black) etc --Sort by individual columns Issues: Format Labels from "Sum of Ugds White" to White Ditto for Black, Hispanic, Asian Wrap the labels: Mouse between rows, expand the spacing - make the rows fatter Format | Wrap On Create Tableau chart, create ratio Grad_Debt_Mdn/Enrollment per school Another Assignment with Graphics Create the following charts in Tableau: Top 10 schools with the most low income debtors and high income debtors. Create jpg, upload to Blackboard Top 10 schools with highest median debt for females, highest for males. Create jpg, upload to Blackboard Write 250 words with your analysis of this data and propose one story idea. Answers due 11:59 pm Sept. 21 on Blackboard Another Assignment with Calculations Calculations in Tableau See Video, Getting Started With Tableau Calculations Follow the exercises in the sample workbook See the transcript for guidance. Homework Question #1: Create a calculated field that divides the profits into sales, which we will call profit-sales ratio. Build a chart that displays the profits-sales ratio by Sub-Category, or tables, machines, fasteners etc. Sort that chart with the highest profit items on top. Format the axis in dollars, label the bars in dollars. Write a headline. Upload a .jpeg into Blackboard. Homework Question #2: Use ARDebt9-17 data Create a histogram plotting the median debt for Arkansas colleges Label the colleges (don't worry, only a portion of the colleges will display. That's ok) Write a headline. Upload a .jpeg into Blackboard.
Introduction to Dashboards Telling a Story With Data: Interactive Dashboards. Search for Dashboard: https://wordpressua.uark.edu/datareporting/tableau-license/ Wells dashboard https://public.tableau.com/profile/rob.wells#!/vizhome/IRE19_GDPVisualizingtheStateLocalEconomy/ArkansasEconomyinDetail Class Exercise: --Build A Dashboard Using Existing Maps and Graphics Pane #1: Map Pane #2: Graphic Pane #3: Graphic with a slider to let readers interact with data (sort by most low-income student debt. sort by highest enrollment by race. sort by highest median debt. etc) Dashboards Create a Dashboard With The Following ElementsPane #1: Map of Default rates for colleges with more than statewide average of White enrollment. Pane #2: Map of Default rates for colleges with more than statewide average of Black enrollment. Pane #3: Map of Default rates for colleges with more than statewide average of Hispanic enrollment.--
Tableau Analytics:
https://help.tableau.com/current/pro/desktop/en-gb/environ_workspace_analytics_pane.htm
Higher Resolution Graphics in Tableau --Embed with Tableau Public Question: "a problem with the map titled "First-Generation Debt Higher than State Average." We are seeing asterisks in areas where schools are being averaged together because of the same zip codes." https://public.tableau.com/profile/mary.hennigan#!/vizhome/Top10ArkCollegeswHighestFirst-GenDebt/ArkFirst-Gen Solution: Drag the College Name (INSTNM) to Tooltip again. That clears up the asterisk problem. https://kb.tableau.com/articles/Issue/asterisks-display-in-tooltip?_ga=2.54541547.179450172.1572394665-1129705566.1555298785 Question: no titles for each graphic, Solution: Drag your graphics into a Dashboard. Then drag the Dashboard into a story. Your headlines will appear that way. Question: fat bars for the chart Solution: Tell the bars to stop eating at Pizza Hut. Question: really, what about the fat bars for the chart Solution: See the attached video.
Question: really, what about the fat bars for the chart
Solution: See the attached video.