tableau2

 

Contents

Calculations, Questions 1-7 ………………………………………………………………………………………………………….. 1 Data Connections, Questions 8 – 14……………………………………………………………………………………………… 12 Analytics, Questions 15 – 19………………………………………………………………………………………………………… 28 Organizing and Simplifying Data, Questions 20 – 23………………………………………………………………………..32 Field & Chart Types, Questions 24 – 29………………………………………………………………………………………….37 Dashboards, Questions 30 – 34 ……………………………………………………………………………………………………. 43 Mapping, 35 – 36 ……………………………………………………………………………………………………………………….. 46

Hands on questions are 4 points each
Knowledge based questions are generally 1 point each, but the multiselect question is worth two points.

Calculations, Questions 1-7

Hands-on Calculations Q 1 of 4

1. Answer the question using this data: https://public.tableau.com/s/sites/default/files/media/HollywoodsMostProfitableStories.csv.
For films where Disney was not the lead studio, what was the % decrease in profitability from 2010 to 2011?

A. 55.3%

B. 7.2%

C. 11.59% D. 86.28%

Add Lead Studio and Profitability to the view:

Change “Year” to discrete:

Add year to the view. The view should be a text table.

Exclude Disney:

Use a table calculation to see the percent difference:

Add column grand totals:

View should now look like this… excluding those with lead studio of Disney, there was a 7.2% drop in 2011.

Hands-on Calculations Q 2 of 4

2. Answer using this data:

https://public.tableau.com/s/sites/default/files/media/TopBabyNamesbyState.csv

For boys born between 2000 and 2012, the most common first letter for the top name by state was: A. W

B. M

C. J

D. D
Get the first letter with this calculation:

Filter on Gender = M and year between 2000 and 2012:

Double-click Number of Records to add it to the view:

Hands-on Calculations Q 3 of 4

3. Answer this using the SuperStore data.
Find the total sales value for all product categories for customers who ordered office furniture.

A. 3,864,069

B. 8,119,645 C. 2,258,326 D. 2,786,637

This calculation is a little tricky. Here’s how it works:

  1. The fixed function means we are looking at all rows for the customer name.
  2. We are taking the max value of the if statement. This if will return a 1 if we purchase officefurnishings.

G. So, if we the customer had any rows where they purchased office furnishings then the MAX function returns a 1, so all of the rows for that customer have a 1.

Once we have this, we can filter on Ever Office Furnishings = 1 to limit to customer who ever purchased office furnishings:

If the value is 1, then the customer purchased Office Furnishings

then add Sales to the view:

Hands-on Calculations Q 4 of 4

4. Answer this question with the Little League data. How many players have the first name “Anna” A. 1

B. 2

C. 3 D. 4

Split the name field to get first and last name:

Add Number of Records and Name – Split1 to the view. You see two records with the name Anna.

5. Knowledge-based: The AVG function does not treat null values as zeros. Rather, the AVG function ignores null values. (TRUE)

6. (NEW) HANDS-ON Question
Using the Players sheet, what percent of pitchers and catchers had a batting average above 0.300 and had fewer than 10 runs?
36.54%
63.46%
19.23%
80.77%
80.23%

Group catcher and pitcher:

Create a calculation for batting avg > .3 and runs < 10:

Now add a formula to count the distinct players:

Add this to the view:

Add a % of total table calculation, and calculate Pane(down) to get the % of the catchers & pitchers with avg > .3 and Runs < 10:

19.23% of pitchers and catchers have avg > .3 and Runs < 10

7. Knowledge-based: An aggregated calculation can be multiplied by a non-aggregated constant (TRUE).

“Constant terms in an expression act as aggregated or disaggregated values as appropriate. For example: SUM(Price*7) and SUM(Price)*7 are both valid expressions.” source

Data Connections, Questions 8 – 14

Hands-on Question 1 of 5

8. Use Bills and Sunlight Info worksheets available in this Excel file to answer the following question. How many failed votes were there for bills sponsored by Colorado Republicans in 2013? (The abbreviation for Colorado is CO)

A. 3

B. 36

C. 78
D. 66
E. 1,188

Join the bills and sunlight info:

Change the Vote Date datatype to date and time:

Filter on Sponsor Party = R

Filter on State = CO

Add number of records to the view:

9. Use this data to answer the following question. New England consists of the following states: Connecticut, Maine, Massachusetts, New Hampshire, Rhode Island, and Vermont. Which year saw the greatest increase in adults on probation in New England?

A. 1998

B. 2002

C. 2009 D. 2011

Select “Use the data interpreter”

Highlight all the columns except the first one, and right-click, and select Pivot:

Rename as “State,” “Year” and “Population”

Result:

Highlight all six New England states, then select Keep Only:

Your view should now look like this:

Add Year and Population to the view and include the totals, so that you see the population for New England each year.

Click on Sum(Population), then click Table Calculation, and select Difference.

Select Table(Down)

View should now look like this. The largest increase occurred in 2002.

10. Answer this question with Teams 2014 and Games worksheets on the Little League data. The Games data includes information on games played in 2015, including showing the team that won each game. Which team had the biggest increase in winning games between 2014 and 2015?

A. Lightning B. Knights C. Bears
D. Ninjas

To answer this question we need to do a BLEND rather than a join. The reason for this – the Games worksheet has one row for each game that was won or lost, while the Teams 2014 worksheet has a single row per team. If you join the two tables by team, you will duplicate the data in the Teams 2014 worksheet. To avoid this, we blend the two worksheets, using the Games worksheet as our primary datasource.

Create a connection to Games:

Click the black arrow and select New Data Source:

Add Team 2014:

Select Data and then Edit Relationships:

Select the “Custom” radio button, click “Add,” then select “Team” and “Winning Team”

Your relationships should look like this:

From Games data, add Winning Team and Number of records to see how many wins each team had in 2015:

If you switch to Games, you should see that “Winning Team” is now the linking field.

Switch to the Team 2014 data, and add Games Won in 2014:

Now we just need the difference:

Add the calculation to the view. You’ll see the Ninja’s had the biggest improvement in games won in 2015 compared to 2014:

11. Answer this question with the Teams 2013 and Teams 2014 worksheets on the Little League data. Which team had the biggest decrease in games won from 2013 to 2014?

A. Lions

B. Bears
C. Tigers
D. Lightning

Both Teams 2013 and Teams 2014 have a single row per team. Each row shows how many games were won for that year. So we will simply join the two datasets.

The default join is by Coach. But since the coach can change from one year to the next, we want to join instead on the name of the team:

Data Source page now looks like this:

Add the Team name, the games won in 2014, and the games won in 2013 to the view. Then add this calculation:

Here is the final view, with the Knights and the Lions both winning three fewer games in 2014 than they did in 2013.

12. (NEW) HANDS-ON Question
Using the Best Buy data, what was the month and year with the largest absolute increase in average Open value from the same month in the prior year?
January 2015
January 2016
March 2016
March 2015
None of the above
Combine all three years of data with a UNION.

Merge the three date fields – “Date 2015,” Date 2014” and “Date”

Now create a view with the Year of the merged date field as a column, and the Month of the merged date field as a row. Add the average Open value to the view:

Now use a table calculation to show the difference in average Open value from the same month in the prior year:

Result shows biggest increase in March 2015:

13. Knowledge-based: When using a _____ join the result is a table that contains all values from the left table and corresponding matches from the right table.
INNER, LEFT, RIGHT, FULL OUTER, BLEND

14. Knowledge-based: If you see duplicate data after doing a join you can do the following: Combine the tables using a cross database join rather than a standard join
Add a duplicate filter to your data
Change the join condition

Change to a full outer join

Explanation: There is no option to filter duplicates. A full outer join can still include duplicates if there are multiple matches on the join condition.
In some situation blending data can avoid duplication you would get with a join, but this wasn’t one of the answers. Changing the join condition can remove duplicates if the original condition was giving more matches that the modified join condition.

Analytics, Questions 15 – 19

15. Answer this question with the Games worksheet from the Little League data. Create a line graph showing away team runs for the Lions by month. Add a linear trendline. What is the R-squared value?

A. -0.1346 B. .6846 C. .7503 D. .0573

Filter on Away Team = Lions

Add Date, and change it to Month:

Add to worksheet, and add Runs for Away Team

Add trendline:

See R-Squared on hover:

16. Answer this question with the Players worksheet from the Little League data. Create a bar chart showing runs by player. Add a reference distribution showing sample standard deviations. What is one standard deviation below the mean?

A. 1.94

B. 27.07 C. 172.58 D. 8.70

Bar chart showing runs by player:

Add distribution band:

Mouse over to see 1 standard deviation below the mean:

17. (NEW) Knowledge: _______ is a tendency to increase or decrease over time A. Seasonality

B. Slope

C. Trend

D. Gradient
18. (NEW) Knowledge: Which of the following graph types provide information on the distribution of a

continuous measure?

  1. Pie Chart
  2. Heat Map
  3. Box Plot
  4. Packed Bubbles

https://onlinehelp.tableau.com/current/pro/desktop/en- us/help.html#qs_boxplots.html?Highlight=box plot
19. (NEW) Knowledge:

Positive skewness means the extreme values are to the right, while negative skewness means the extreme values are to the left.
TRUE https://onlinehelp.tableau.com/current/pro/desktop/en-us/inspectdata_summary.html

Organizing and Simplifying Data, Questions 20 – 23

20. Hands-on: Answer this question with the Players worksheet on the Little League data. Create a hierarchy Baseball Team – Position. For the Ninjas, how many runs were scored by the position with the lowest average batting average?

A. 150

B. 70

C. 54

D. 24
You can create a hierarchy by dragging Position to Baseball team:

Click OK

Add the hierarchy to the view. Add batting average, changing the calculation to Average. Add Runs. Change the format so you can see the full decimals for average batting average:

It looks like catcher has the lowest batting average, and 54 runs:

21. Hands-on: Answer this question with the Players worksheet on the Little League data. What percent of runs were scored by the top three teams with the highest average batting average?

A. 7.73% B. 8.97% C. 34.55% D. 92.27%

Add baseball team and runs, and change the calculation to Average to show average batting average:

Create a set for top three average batting average:

Add runs and change to “Percent of Total”

Add subtotals:

Final view:

Add subtotals:

Final view, showing 34.55% of total runs from the top three teams by batting average:

22. When you sort data in a hierarchy, Tableau rearranges headers that appear before the sorted field.

(FALSE)

“Tableau does not rearrange [hierarchy] headers that appear before the sorted field.” https://onlinehelp.tableau.com/current/pro/desktop/en- us/sortgroup_sorting_computed_ex2hierarchy.html

23. Context Filters are executed after data source filters. (True)
Context filters are executed after data source filters but before dimension source filters. https://onlinehelp.tableau.com/current/pro/desktop/en-us/order_of_operations.html

Field & Chart Types, Questions 24 – 29

24. Answer this using the SuperStore data.
What is the maximum number of months between the ship date and the order date? How many orders took this many months to ship?

  1. 5 months, 1 order
  2. 5 months, 2 order
  3. 3 months, 2 order
  4. 3 months, 1 order

e. None of the above Create a calculation “Months to ship”

Add calculation and change to max:

You will see that the maximum months to ship = 3. Add a filter where months to ship = 3

Right-click and view data:

Select full data to see that two orders took three months to ship:

25. Hands-on: Answer this question with the Games worksheet on the Little League data. Find the moving average for the total number of runs in three months prior to May 2015.

A. 749.3 B. 753.8 C. 796.0 D. 753.8

Calculate Home + Away runs:

Create a line graph showing runs by month. Add a moving average:

Edit table calculation:

Use the three prior values, not including the current value. This way the May 2015 value will show the average of the three prior months (February, March, April)

Mouse-over on May to see the average.

26. Knowledge (TWO POINTS): Tableau displays an axis when you drop this onto the Rows or Column shelf.

Continuous field

Discrete field
Measure names
Measure values
Tableau displays and axis when you drop a continuous field onto the row or columns shelf. The automatically generated Measures Values field is always a continuous field.

Continuous and Discrete: http://onlinehelp.tableau.com/current/pro/desktop/en- us/datafields_typesandroles_dataroles_continuousdiscrete.html
Measure Names and Measure Values: https://onlinehelp.tableau.com/current/pro/desktop/en- us/datafields_understanddatawindow_meavalues.html

27. Answer this question with the Players worksheet on the Little League data. Create a histogram showing batting averages using a bin size of .05. Find the average number of runs scored for players with a batting average between .55 and .6.

A. 13.53

B. 15.64 C. 18.92 D. 13.38

Add batting average to the view, then open the “Show Me” menu and select the Histogram icon.

Change the bin size from .0486 to .05:

Drag Runs to tooltip:

Switch from SUM(Runs) to AVG(Runs) and mouse over the .55 to .6 bin to see the average runs:

28. Knowledge: Gantt charts are useful for:

  1. Showing changes to a continue variable over time
  2. Displaying duration of events or activities over time
  3. Showing how a measure varies with respect to one or more dimensions
  4. Display the distribution of a continuous variable

“Use Gantt charts to show the duration of events or activities.”

https://onlinehelp.tableau.com/current/pro/desktop/en-us/buildexamples_gantt.html

29. Knowledge: Bins acts as containers that summarize data for a specific range of values. Without using a calculated field, Tableau’s bin feature allows you to create variable sized bins. (FALSE)

If you want to create variable sized bins, you’ll need to create a calculated field with IF THEN ELSE statements. An example is shown here: http://kb.tableau.com/articles/knowledgebase/creating- variable-sized-bins

Dashboards, Questions 30 – 34

30. Answer this question using the Tableau Jobs dashboard. What percent of job postings for Developers mention Python skills?

A. 13%

B. 15%

C. 17% D. 19%

Select “Developer” on the top part of the dashboard. You should see the bar chart below change – you are now filtering to show only job postings for developers. Mouse over Python:

31. Hands-On: Answer this question using the Tableau Jobs dashboard. For the Business Intelligence position, what is the Title with the most job postings?
BI Developer
Business Intelligence Analyst

Data Analyst Business Analyst

You can see this by expanding the hierarchy:

BI Developer has 13 associated postings, more than any other for the business intelligence position.

32. If a dashboard is difficult to view on a smartphone, you can use the _____ designer to create a phone-specific view of your dashboard:

Custom layout

Device Designer

Floating layout Narrower width

http://www.tableau.com/about/blog/2016/8/tips-designing-device-specific-dashboards-make- everyone-happy-57548

  1. Knowledge: A ___ action allows Tableau to open a page on an external website. URL, Filter, Highlight, Web“A URL action is a hyperlink that points to a Web page, file, or other web-based resource outside of Tableau.”
    https://onlinehelp.tableau.com/current/pro/desktop/en-us/actions_url.html
  2. Knowledge: You can combine multiple worksheets in a single dashboard. (TRUE)
    “A dashboard is a collection of several worksheets and supporting information shown in a single place so you can compare and monitor a variety of data simultaneously.” http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#dashboards.html

Mapping, 35 – 36

35. Answer this question using the Tableau Jobs dashboard. Create a map view and zoom in to southern California. How many jobs are either in Costa Mesa or within 5 miles of Costa Mesa?

A. 0 B. 2 C. 5 D. 8 E. 10

On the Jobs By City worksheet, do a search for Costa Mesa, California:

Use the radial selection tool:

Click on Costa Mesa and drag a 5 mile radius:

When you release you will see the number of posts within the 5.0 radius:

36. Using the Tableau Job Market dashboard, determine which group of three states have the most job posts:

Minnesota, Wisconsin, and Michigan

Texas, New Mexico, Oklahoma

Florida, Georgia, Alabama

Arizona, Utah, Colorado
You can group the states like this:

Remove State from the view to see the new Custom Territories:

Alabama, Florida, and Georgia have 68 postings, more than any of the other three state groups:

Advertisements
%d bloggers like this: