Tableau Advanced Training. Student Guide July For evaluation only

Tableau Advanced Training ly Student Guide Fo re va lu at io n on www.DataRevelations.com 914.945.0567 July 2015 – 9.0 © 2011 – 2015 Data ...
Author: Maria Carpenter
3 downloads 2 Views 2MB Size
Tableau Advanced Training

ly

Student Guide

Fo

re

va

lu at io

n

on

www.DataRevelations.com 914.945.0567

July 2015 – 9.0

© 2011 – 2015 Data Revelations. All rights reserved.

Fo

re

va

lu at io

n

on

ly

Portions copyright Tableau Software.

Contents A.

Warm Up

1

Bar Chart Colored by Profit Salary Curve Quota

VII.

1 2 3

Programmatic and Combined Sets

4

Joins and Blends

on

VIII.

ly

Overview To Create the Top 50 by Sales and Bottom 50 by Profit Sets To Create a Combined Set Showing the Intersection of the Two Existing Sets To Create a Scatterplot that Shows the Problem Products

9

re

va

lu at io

n

Join Overview To Connect To Two Tables Using a Join To Create a Chart that Shows Location and Magnitude of Sales on a Map Coloring the Dots – What Happens when We Try to Color the Dots by Expectation? To Color the Dots by Expectation and Change the Mark Type to a Pie How to Interpret the Pie Slices Bonus Exercise – Show How Expectations and Sales Wax and Wane over Time Blend Overview To Get Familiar with the Two Databases To Create a View that Shows Combined Sales for Region / Market Blending Using Scaffolding -- Showing Combined Sales by State To Show Sales from Two Data Sources Broken Down By State using Scaffolding To Show Combined Sales

IX.

Advanced Table Calculations

4 4 6 7

9 10 12 12 12 14 14 15 16 17 20 21 23

26

Fo

Moving Average 26 To Create a Visualization that Combines Actual Sales and Moving Average 26 To Turn the Tableau Calculation into a Reusable Custom Calculation 27 Top N Within a Category 28 To View the Top 10 Customers by Region Using a Context Filter 30 To See the Top 10 Customers by Region Showing All Regions at Once Using RANK() 31 To See the Top 10 Products by Region Showing All Regions at Once Using RANK() 33 Visual Ranking within a Category 35 To Create the Overall View 35 To Create the Break Down by Region View 36 To Create a Dashboard the Combines the Two Views 37 Deploying Guided Analytics 39 To Add a Filter so that a Second Sheet Appears Only when Something is Selected in The First Sheet 39 Adding Color and Making Life Easier with the ATTR() Function 41 Definition of ATTR() 43 To Color The Top 20 Overall Bars by ATTR(Product Category) 44 Visual Ranking by Selectable Categories 45

Contents ● i

To Create a Parameter-Driven Visual Ranking Dashboard Pareto Analysis To Create a Basic Pareto Visualization To Create a Better Pareto Visualization To Create a Dual Axis Pareto Chart

X.

Sophisticated Dashboard Design

55

on

Jittering (aka “Strip plots”)

ly

Overview To Get Started with the File To Create a Dashboard, Specify Size, and Place Worksheets To Format the Dashboard To Add Filters, a Logo, and a Help “Button” To Create a Filter Action To Create a Highlight Action To Create a URL Action Bonus Exercise

XI.

lu at io

n

Overview To Create a Simple Jitterplot To Clean Up the Visualization and Add Reference Lines Controlling Jittering To Create a More Sophisticated Jitterplot

XII.

Ratios and Key Performance Indicators (KPIs)

re

va

Overview Developing a Profit Ratio Formula – Ratio of the Sums vs. Sum of the Ratios To Create a Possible Profit Ratio Calculated Field To Determine the Flaw and Create and Accurate Profit Ratio To Create a Profit Ratio Text Table To Create a KPI Grid To Create a Combined Bar and KPI Chart

Level of Detail Expressions (LoD Expressions)

Fo

XIII.

Overview To Calculate the Average Order Broken Down by State Using LoD Expressions to Determine How Many Customers Placed a Certain Number of Orders To Create a Histogram Showing Number of Orders Placed

XIV.

Using Parameters to Change Charts

Overview To Create a Parameter and the First True / False Field To Create the Additional True / False Fields To Create a Dashboard That Changes Chart Views

ii ● Contents

45 48 48 50 53

55 56 56 58 59 61 63 63 64

66 66 68 70 72 73

76 76 76 76 78 80 81 82

85 85 86 88 88

90 90 91 92 93

Visual Ranking within a Category The Top 10 Products by Region is certainly useful but it does take some work to see the different rankings across regions. It would also be even more difficult to see more than 10 or so products as there wouldn’t be enough room on the screen without having to scroll. In the next series of examples you will create a dashboard that shows the Top 20 Products

re

va

lu at io

n

on

ly

overall and how each product fares across four different regions.

We’ll start first by creating the Overall view and then create a view that shows the break down by Region.

Fo

To Create the Overall View 1.

Click the New Worksheet tab.

2.

With Orders (Superstore Sales_DR.xls) as your data source, drag Product Name onto the Rows shelf.

3.

Drag Sales onto the Columns shelf.

4.

Sort Product Name in descending order.

5.

Drag Rank of Sales to the Filters shelf and click OK when the Filter dialog box appears.

6.

Right-click Rank of Sales in the Filters shelf and change Compute Using to Product Name.

Advanced Table Calculations ● 35

7.

In the Filters dialog box, change the range of values to go from 1 to 20, as shown be-

on

ly

low.

8.

Control-drag Rank of Sales to the Filters shelf and drop it on the Rows shelf.

9.

Change Rank of Sales on the Rows shelf from a continuous measure to discrete by

n

right-clicking it and selecting Discrete.

lu at io

10. Move the now discrete version of Rank of Sales to the left of Product Name, as

Fo

re

va

shown below.

11. Rename the sheet Top 20 Overall and save your work.

To Create the Break Down by Region View 1.

Duplicate the Top 20 Overall worksheet.

2.

Drag Region onto the Columns Shelf. You will see a lot of blank rows.

36 ● Advanced Table Calculations

3.

Drag Product Name off of the Rows shelf and place it on the Level of Detail by dragging it on top of the Detail button on the Marks card. Your screen should look

Rename the worksheet Rank by and save your work.

re

4.

va

lu at io

n

on

ly

like the one shown below.

Fo

To Create a Dashboard the Combines the Two Views 1.

Click the New Dashboard tab.

2.

Change the width to 900 and the height to 600.

3.

Rename the tab Visual Ranking by Category and indicate you want to show the title by selecting Show Title from the bottom left corner of your screen.

4.

Drag Top 20 Overall onto the dashboard.

5.

Drag Rank by to the right of Top 20 Overall.

6.

Select Top 20 Overall and indicate that you want the worksheet to Fit Width.

7.

Select Rank by and indicate you want the worksheet to Fit Width.

8.

From the Dashboard menu select Actions.

9.

When the Actions dialog box appears, click Add Action and select Highlight.

Advanced Table Calculations ● 37

10. Name the action Highlight by Product Name. 11. Indicate that you want Run action on to be Select, the source sheet to be Top 20 Overall, the target sheet to be Rank by, and that Target Highlighting should use

re

va

lu at io

n

on

ly

Product Name as the selected field, as shown below.

Fo

12. Click OK to dismiss the first dialog box and OK again to dismiss the second dialog box.

38 ● Advanced Table Calculations

13. Try clicking different Product Names in the Top 20 Overall sheet and see the results

lu at io

n

on

ly

in the Rank by sheet, as shown here.

Deploying Guided Analytics As you have probably gathered, the second visualization only makes sense when you select something from the first visualization. We should make it so that if nothing is selected in the

va

first visualization the second visualization should be blank. We can achieve our results by filtering the second visualization with a dummy field (we’ll call

re

it “Blank”) and by setting the Action so that clearing the selection will Exclude all values.

Fo

To Add a Filter so that a Second Sheet Appears Only when Something is Selected in The First Sheet 1.

Go to the sheet called Top 20 Overall.

2.

Create a field called Blank that is defined as follows.

Note that the field can contain any text value. 3.

Drag Blank to the Level of Detail.

4.

Go to the Visual Ranking by Category dashboard.

5.

From the Dashboard menu select Actions.

Advanced Table Calculations ● 39

6.

When the Actions dialog box appears click Add Action and select Filter.

7.

Rename the Action Filter by blank.

8.

Make the source sheet Top 20 Overall and the Target sheet Rank by.

9.

Indicate that Run action on should be Select, Clearing the Selection should Exclude

Fo

re

va

lu at io

n

on

ly

all values, and the Target Filters should be Blank, as shown below.

Note that you will need to click the Add Filter button to add “Blank” as a Target Filter. 10. Click OK twice to dismiss all open dialog boxes. 11. See what happens when you select a Product name in the Top 20 Overall sheet and then what happens when you clear the selection (i.e., press the Escape key.) 12. Save your work.

40 ● Advanced Table Calculations

Adding Color and Making Life Easier with the ATTR() Function Consider the Top 20 Overall visualization. Let’s suppose we decide to color each of the bars by Product Category. Let’s see what happens when we drag Product Category to the Color

lu at io

n

on

ly

button.

va

Something is forcing Tableau to create a separate row for each Product Category. We can

Fo

re

dissect this by right-clicking Rank of Sales and selecting Edit Table calculation.

We can dig a little deeper by changing the Compute Using setting to Advanced, which will bring up the following dialog box.

Advanced Table Calculations ● 41

ly on n

Placing Product Category on color has added an additional partition to the mix in that Tableau

lu at io

will think it has to “restart” whenever it encounters w new Product Category.

Fo

re

va

One solution is to move Product Category from Partitioning to Addressing, as shown below.

This will in fact give us the results we want, as shown here.

42 ● Advanced Table Calculations

ly

on

Same visualization but a change to how the table calculation is addressed and partitioned.

function.

lu at io

Definition of ATTR()

n

While this will work there’s an easier way to get what we want and that’s to use the ATTR()

Here’s the official definition of the function. ATTR(expression)

Returns the value of the given expression if it only has a single value for all rows in

va

the group, otherwise it displays an asterisk (*). Null values are ignored. A very nice side effect of this function is that it will label data without breaking it into addi-

re

tional partitions.

This means that we can get the coloring we need without generating the additional rows by

Fo

placing ATTR(Product Category) on the color shelf.

Advanced Table Calculations ● 43

To Color The Top 20 Overall Bars by ATTR(Product Category) 1.

Go to the sheet named Top 20 Overall.

2.

Right drag Product Category onto the Color button on the Marks card and select

lu at io

n

on

ly

ATTR(Product Category). Your screen should look like the one shown below.

3.

Go to the sheet named Rank by and repeat the steps. Note that the sheet may not

Fo

re

va

display given the state of the Action filter in the Filters shelf.

4.

Go to the dashboard and try selecting and de-selecting different Product Names.

44 ● Advanced Table Calculations

Visual Ranking by Selectable Categories In the previous example we created a dashboard that shows ranking by Region. In this next example we’ll make it so that we can select a parameter that will allow us to show ranking by any of the following dimensions: 

Region



Customer Segment



Year

To Create a Parameter-Driven Visual Ranking Dashboard Go to the sheet named Rank by.

2.

Remove the Action (Blank) filter from the Filters shelf.

3.

Create a parameter called Select Category and define it as follows.

Fo

re

va

lu at io

n

on

ly

1.

4.

Click OK when you are done editing the parameter.

5.

Right-click the newly-created Select Category parameter in the bottom right portion of your screen and select Show Parameter Control.

Advanced Table Calculations ● 45

Create a new calculated field called Break down and define it as follows.

7.

Click OK when you are finished editing.

8.

Drag Break down on top of Region to replace Region with the new field.

lu at io

n

on

ly

6.

(You can also just remove Region and place Break down on the Columns shelf.) Click the Worksheet menu and select Show Title.

va

9.

10. Right-click the now visible title and select Edit Title. The Edit Title dialog box will

re

appear.

Fo

11. After type a space and click the Insert button towards the top right of the dialog box.

12. Select Parameters.Select Category and click OK. 13. Go to the Visual Ranking by Category dashboard. 14. From the Analysis menu select Parameters and select Select Category.

46 ● Advanced Table Calculations

15. Make the Select Category parameter a floating element and drag it towards the bottom left corner, as shown below.

Select the small arrow and

lu at io

n

on

ly

select Floating from the menu.

16. Try selecting different values from the Select Category parameter drop down list and then select an item from the Top 20 Overall visualization.

Fo

re

va

17. Save your work.

Advanced Table Calculations ● 47