Tableau Classroom Training TABLEAU DESKTOP FUNDAMENTAL
Proprietary Notice
The materials in this document are proprietary to PT CYBERTREND INTRABUANA (CBI). In accepting this document, it is deemed that participant has agreed to abide by the confidentiality nature of this document. The reproduction or distribution of any part of the enclosed information will not be allowed without prior written permission of CBI. Any other parties other than approved user by CBI who review this document will be deemed to have obtained CBI’s proprietary information without authorization and may be subject to legal actions.
Disclaimer
This document is supplied in strict confidence and must not be produced in whole or in part, used in tendering or for manufacturing purposes or given or communicated to any third party without the prior consent of CBI.
Copyright @2014 Cybertrend-Intrabuana Training Center
2
Table of Contents How to Use These Material .................................................................................................................................... 5 For Mac Users ......................................................................................................................................................... 5 Keyboard and Mouse Differences ...................................................................................................................... 5 Visual Differences ............................................................................................................................................... 5 Additional Note for Mac Users ........................................................................................................................... 5 Introduction to Tableau Fundamental Desktop ..................................................................................................... 6 Introduction ............................................................................................................................................................ 7 Tableau Product Overview ................................................................................................................................. 8 Tableau Mission ............................................................................................................................................. 8 Tableau Product ............................................................................................................................................. 9 Tableau Desktop Installation ............................................................................................................................ 11 Tableau Desktop Architecture .......................................................................................................................... 12 Tableau File Types ............................................................................................................................................ 13 Keyboard Shortcut ............................................................................................................................................ 13 General Keyboard Shortcut .......................................................................................................................... 13 Navigation and Selection Shortcuts ............................................................................................................. 15 Field Selection Shortcuts .............................................................................................................................. 16 Connecting To Data .............................................................................................................................................. 17 How To Connect With Your Data...................................................................................................................... 18 Practice : Connect With Your Data ............................................................................................................... 19 Connection Type ............................................................................................................................................... 20 JOIN Database Table......................................................................................................................................... 21 Join Types ..................................................................................................................................................... 22 Practice : Use Extract Connection and Join Order and Returns ....................................................................... 23 Creating Basic Visualization .................................................................................................................................. 24 Tableau Desktop Workspace ............................................................................................................................ 25 Analysis Via Show Me ....................................................................................................................................... 30 Practice : Make your first view .................................................................................................................... 32 Simplifying and Sorting Your Data ........................................................................................................................ 33 Filtering Your Data ............................................................................................................................................ 34 Bar Chart, Stacked Bar, Side By Side Bars..................................................................................................... 35 Sorting Data ...................................................................................................................................................... 40 Organizing Your Data ............................................................................................................................................ 42 Groups .............................................................................................................................................................. 43 Hierarchies........................................................................................................................................................ 43 Combined Field ................................................................................................................................................. 44
3
Using Multiple Measure on the Same Axis ........................................................................................................... 45 Using Multiple Measure ................................................................................................................................... 46 Individual Axis .............................................................................................................................................. 46 Blended Axis ................................................................................................................................................. 46 Dual Axis ....................................................................................................................................................... 47 Combination Charts...................................................................................................................................... 48 Practice : Dual Axis with Combine Chart ......................................................................................................... 50 Practice : Multiple measure in 1 Axis .............................................................................................................. 50 Mapping Data Geographically .............................................................................................................................. 53 Maps (Symbol and Filled) ................................................................................................................................. 54 Viewing
Specific Values and Distributions .................................................................................................... 56
Text Tables Crosstab ......................................................................................................................................... 57 Highlight Tables ................................................................................................................................................ 57 Grand Totals and Aggregation .......................................................................................................................... 58 Grand Total Aggregation .............................................................................................................................. 59 Customizing Your Data ......................................................................................................................................... 60 Calculated Fields ............................................................................................................................................... 62 Practice : Calculation Profit Ratio ................................................................................................................ 63 Logical Statement ............................................................................................................................................. 65 Practice : Is Profitable? ................................................................................................................................ 66 Type Conversion ............................................................................................................................................... 67 Quick Table Calculations................................................................................................................................... 67 Making Your View Available ................................................................................................................................. 70 Dashboard ........................................................................................................................................................ 71 Actions .............................................................................................................................................................. 73 Filter Actions ................................................................................................................................................ 73 Highlight Actions .......................................................................................................................................... 76 URL Actions .................................................................................................................................................. 81 Practice : Dashboard Sales .......................................................................................................................... 84 Sharing Your Work ................................................................................................................................................ 85 Export ............................................................................................................................................................... 86 Export as image ............................................................................................................................................ 86 Export as Data .............................................................................................................................................. 86 Exporting Data Source .................................................................................................................................. 88
4
How to Use These Material This book is yours to keep, and serves both as instruction material and continuing resource for the concepts you learn in the course. The lessons in this book are organized by subject into modules. Each module includes conceptual information, how-to steps and practices for concept learned. At the end of the instruction materials are two appendices, which include additional useful information, Tableau Desktop Details and solution to all the practices.
For Mac Users The instructions and images in this book were created using the windows operation system, so people running Tableau Desktop on Mac OS may experience a few differences when doing the activities in the book.
Keyboard and Mouse Differences T ABLE 1- K EYBOARD AND M OUSE D IFFERENCES
Windows-Based Instruction
Difference on a Mac
CTRL+Click
Press and hold the command key while you click
Right-click
When using a mouse with no right-click button, press and hold the control key while you click
Right-click and drag
Press the option (Alt) key, and hold it down while you click and drag.
Shift+Drag
Press the key while you click and drag
Visual Differences The windows-based instructions and images in this book may indicate that the X Button to close a dialog box or window is in the top right corner, but on Mac OS, these buttons may be located in the top left corner instead. There may also be small differences in the appearance and location of tabs, drop down menus and another visual features of Tableau Desktop, but the use and functionality of those features in the same both operating systems.
Additional Note for Mac Users When you open multiple workbooks in Tableau Desktop on a Mac, multiple instances of the application are created, each with its own icon in the Dock. This differs from typical Mac application behaviour, where one instance of the application handles all open files managed by that application.
5
Introduction to Tableau Fundamental Desktop Welcome to Tableau Fundamental Desktop training. Our goal for this course is to help you implement important concepts and techniques in Tableau Data visualizations and combine them in interactive dashboards so you can get the most out of your data. This class is organized into these following modules :
Introduction Connecting to Data Creating Basic Visualization Work With Your Data Customizing Your Data Using Parameters for Dynamic Values Highlight Data With Reference Line Showing Data History Building Dashboard More Visualization Mapping Data Geographically Sharing Your Work
6
Introduction In this module, you’ll learn about these concepts:
Tableau Desktop and Tableau Product Line Application Terminology Visual Cues for Fields
Fast Analytics and Rapid-fire Business Intelligence for Everyone
7
Tableau Product Overview Tableau Mission We help people see and understand data. Seven words that drive everything we do. And they’ve never been more important. In 2020 the world will generate 50 times the amount of data as in 2011. And 75 times the number of information sources (IDC, 2011). Within these data are huge, unparalleled opportunities for human advancement. But to turn opportunities into reality, people need the power of data at their fingertips. Tableau is building software to deliver exactly that. Our products are transforming the way people use data to solve problems. We make analyzing data fast and easy, beautiful and useful. It’s software for anyone and everyone. We are Tableau Software.
F IGURE 1 - TABLEAU M ISSION
8
Tableau Product
Commercial
Free
- Personal Edition - Professional Edition
Tableau Public
F IGURE 2 - TABLEAU P RODUCT
o
Tableau Desktop Tableau Desktop is desktop based application, Data analysis application that keeps up with you. It’s easy to learn and use. It’s built on breakthrough technology that translates pictures of data into optimized database queries.
o
Tableau Server Tableau Server is browser and mobile based insight anyone can use. You can publish data source, data connection or dashboard with tableau Desktop and share them throughout your organization. It’s easy to set up and even easier to run.
o
Tableau Reader Tableau Reader is a free desktop application that you can use to open and view visualizations built in Tableau Desktop. With Tableau Reader you’ll be able to open and interact with visualizations built with Tableau Desktop, data included. You can filter, drill down and view details of the data as far as the author allows. You won’t be able to edit or perform any interactions if the author hasn’t built it
o
Tableau Public Tableau public is for anyone who wants to tell interactive data stories on the web. Its delivered as a service so you can be up and running right now. You’ll be able to connect to data, create interactive data visualizations and publish them directly to your website.
o
Tableau Online
9
Tableau Online is a hosted version of Tableau Server. Publish dashboard with Tableau Desktop and share them with colleagues, partners or customers Online.
F IGURE 3 - TABLEAU D ESKTOP F EATURE C OMPARISON
10
Tableau Desktop Installation If you already have Tableau Desktop installed on your PC, you can skip this section and go to the next section. Tableau offers a free software trial if you do not already own a license. The program requires a PC running Microsoft Windows (Version 7 or Version 8), Vista or XP, and you must have administrative rights on your computer to install it. It is important to note that Tableau has been tested and is supported on 64-bit Windows versions. Tableau is a 32-bit application and requires 32-bit versions of the database drivers, even when running on 64-bit versions of Windows. Tableau desktop can be download at http://www.tableausoftware.com/products/desktop
F IGURE 4 - T ABLEAU D ESKTOP INSTALLATION
F IGURE 5 - T ABLEAU D ESKTOP INSTALLATION
11
Tableau Desktop Architecture
F IGURE 6 - TABLEAU D ESKTOP A RCHITECTURE
In 2003 Tableau spun out of Stanford University with VizQL™, a technology that completely changes working with data by allowing simple drag and drop functions to create sophisticated visualizations. The fundamental innovation is a patented query language that translates your actions into a database query and then expresses the response graphically. The next breakthrough was the ability to do ad-hoc analysis of millions of rows of data in seconds with Tableau's Data Engine. The Data Engine is a high-performing analytics database on your PC. It has the speed benefits of traditional in-memory solutions without the limitations that your data must fit in memory. And in Tableau's tradition of making powerful tools accessible to all, there’s no custom scripting needed to use the Data Engine.
12
Tableau File Types
F IGURE 7 - TABLEAU FILE TYPES
Keyboard Shortcut General Keyboard Shortcut Keyboard Shortcut
Description
Windows
Mac
Ctrl+A
A
Select all data
Ctrl+B
B
Smaller cell size
Ctrl+Shift +B
B
Bigger cell size
Ctrl+C
C
Copy selected data
Ctrl+Alt+C
C
Place selected field on Columns shelf
Ctrl+D
D
Connect to data source
Ctrl+E
E
Describe sheet
Ctrl+F
F
Makes the find command in the Data window active
Ctrl+Alt+F
F
Place selected field on Filters shelf
^F
Enter/Exit Full Screen
Enter
Switch in and out of Presentation Mode
F7 , Ctrl+H
13
Ctrl+Alt+I
I
Place selected field on Size Flip orientation of column labels at bottom of view
Ctrl+L Ctrl+Alt+L
L
Place selected field on Detail
Ctrl+M
T
New worksheet
Ctrl+N
N
New workbook
Ctrl+O
O
Open file
Ctrl+Alt+O
O
Place selected field on Color
Ctrl+P
P
Print
Ctrl+Alt+P
P
Place selected field on Pages shelf
Ctrl+Alt+R
R
Place selected field on Rows shelf
Ctrl+S
S
Save file
Ctrl+Alt+S
S
Place selected field on Shape
Ctrl+Alt+T
T
Place selected field on Text/Label
Ctrl+V
V
Paste clipboard
Ctrl+W
^W
Swap rows and columns
Ctrl+X
X
Cut text selection (e.g., in captions, titles, formulas, etc.)
Ctrl+Alt+X
X
Place selected field on Rows shelf
Ctrl+Y
Y
Redo
Ctrl+Alt+Y
Y
Place selected field on Columns shelf
Ctrl+Z
Z
Undo
Ctrl+Alt+Backspace
delete Clear the current worksheet
Ctrl+(left arrow)
^
Make rows narrower
Ctrl+(right arrow)
^
Make rows wider
Ctrl+(down arrow)
^
Make columns shorter
Ctrl+(up arrow)
^
Make columns taller
Ctrl+1 , Ctrl+Shift+1 , Ctrl+!
1
Show Me!
Enter
Return
Add the selected field to the sheet. Only works with a single field
F1
?
Opens the Help Deletes the selected sheet (on a dashboard)
Ctrl+F4 Alt+F4
W
Closes the current workbook
14
F4
Starts and stops forward playback on the pages shelf
Shift+F4
Starts and stops backward playback on the pages shelf
F5
R
Refreshes the data source
Ctrl+.
.
Skip forward one page
Ctrl+,
,
Skip backward one page
Ctrl+Tab , Ctrl++F6
}
Cycle forward through open worksheets
Ctrl+Shift+Tab , Ctrl+Shift+F6 {
Cycle backward through open worksheets
F9
0
Run update
F10
0
Toggles Automatic Updates on and off
F12
E
Reverts workbook to last saved state
Esc
Esc
Clears the selection (Desktop and Reader only)
Navigation and Selection Shortcuts Keyboard/Mouse Action
Description
Windows
Mac
Click
Click
Selects the mark
Drag
Drag
Selects a group of marks
Ctrl+Click
Click
Adds individual marks to the selection
Ctrl+Drag
Drag
Adds a group of marks to the selection
and Drag , Click Hold+Drag
Shift+Drag , Click Hold+Drag
and Pans around the view
Doubleclick , Ctrl+Shift+Click
Double-click , Click
Zooms into a point in the view (requires zoom mode if not map)
Ctrl+Shift+Alt+Click
Click
Zooms out from a point on a map (requires zoom mode if not map)
Shift+Double-click
Double-click
Zooms out
Ctrl+Shift+Drag
Drag
Zooms into an area in the view (requires zoom mode if not map)
Ctrl+Scroll
Scroll
Zooms in and out on a map (Desktop and Reader only)
Click+Drag pane+Hold
to
bottom
of Click+Scroll, Hold
Drags a row and scrolls through a long list simultaneously
15
Field Selection Shortcuts Keyboard/Mouse Action Windows Right-click+Drag shelf
Description Mac
to Drag shelf
to Opens the Drop Field menu
Ctrl+Drag
Drag
Copies a field in the view to be placed on another shelf or card
Double-click
Double-click
Adds a field to the view
16
Connecting To Data In this module, you’ll learn about these concepts:
Creating a Live Data Connection Saving and Sharing a Data Source Understanding Changes to Data Other Data Connection Option in Tableau
Live / In Memory Data
17
How To Connect With Your Data When you open Tableau you are taken to the home page where you can easily select from previous workbooks, sample workbooks, and saved data sources. You can also connect to new data sources by selecting Connect to Data. The option in a File is for connecting to locally stored data or file based data. The options listed beneath On a Server link to data stored in a database, data cube, or a cloud service.
Although all of these databases have very different ways of storing and looking up data, the pop-up window is very user friendly and requires little or no understanding of the underlying technology. Most of these databases will require you to install a driver particular to each tool. Installation normally requires a few minutes and you can find all the connectors at: http://www.tableausoftware.com/support/drivers. Data sources Accessible to Tableau Desktop
F IGURE 8 - D ATA S OURCE A CCESSIBLE TABLEAU D ESKTOP
18
Practice : Connect With Your Data From the Starter folder, open the package workbook named First Starter.twbx and follow these step :
Select Connect to Data
Connect to Superstore sample spreadsheet (you can find at folder data)
F IGURE 9 - C ONNECT W ITH Y OUR D ATA
The connection window will display Click and drag the Orders sheet / table into blank area, Tableau automatically preview your data Use Live Connection Type Click Go to Worksheet
Connection Type Click and drag the sheet / table name Data Filters
There are three sheets / table in XLS File. We can use single table / multiple table
F IGURE 10 - C ONNECT W ITH Y OUR D ATA
19
Now, select product and drag it into Rows Shelf You’ll see the data
Open the excel file and change ‘central’ into ‘Pusat’.
Connection Type There are 2 connection types in Tableau : Live (Direct Connect) and In Memory (Data Extract). Direct connections allow you work with live data. When you extract data you import some or all of your data into Tableau’s data engine. This is true in Tableau Desktop and Server.
Direct Connect Connecting to your datasource with a direct connection means you are always visualizing the most up-to-date facts. If your database is being updated in real-time you only need to refresh the Tableau visualization via the F5 function key or by right-clicking on the datasource in the data window and selecting the Refresh option. 20
If you connect to massive data, the visualization is very dense, or your data is in a high-performance enterprise-class database, you may get faster response time with a direct connection. Choosing a direct connection doesn’t preclude the possibility of extracting the data later. You can also swap from an extract to a live connection by right-clicking the datasource and un-checking the Use Extract option.
Data Extract Data extracts don’t have the advantage real-time updating that a direct connection provides, but using Tableau’s data engine provides a number of benefits: Performance improvement Perhaps your primary database is already heavily loaded with requests. Using Tableau’s data engine enables you to split the load from your primary database server to the Tableau Server. Tableau’s extract may be updated daily, weekly, or monthly during off-peak hours. Tableau’s Server can also refresh extracts incrementally and in time intervals as low as fifteen minutes. Additional functions If your datasource is from a file (Excel, Access, text) doing an extract will add calculation functions (median and count distinct) that are not supported by the datasource. Data portability Extracts can be saved locally and used when the connection to your datasource is not available.
JOIN Database Table Seldom will your datasource include every bit of information you need in a single table. Even if you normally connect to Excel it may be advantageous to use related data from more than one tab. As long as the data resides in a single spreadsheet or database and each table includes unique identifiers that tie the tables or tabs together, you can perform joins of these tables within Tableau. These identifiers are called Key Records. In Tableau, you can define joins when you make your initial data connection or add them later.
F IGURE 11 - J OIN O RDERS AND RETURNS
21
Join Types
Inner Join Returns only records that match in both the left and right Tables.
F IGURE 12 - J OIN T YPES > I NNER JOIN
Left / Right Join Selecting left gives priority to the original table. Selecting right gives priority to the new table. The Left Join returns every record in the orders table plus the matching records in the returns table. The Right Join gives priority to the right returns table
F IGURE 13 - J OIN T YPES > L EFT J OIN
F IGURE 14 - J OIN T YPES > R IGHT JOIN
Full Outer Join Returns all the records.
F IGURE 15 - J OIN T YPES > F ULL OUTER J OIN
22
Practice : Use Extract Connection and Join Order and Returns From the previous starter, edit the connection and change the connection type into extract. Left Join Order and Returns Table. So, you can see the order status. Step : 1. 2. 3. 4.
Right Click on Data and select Edit the Datasource Click Extract on Connection Types Drag returns table into panel Click on join types and change it into left join
F IGURE 16 - J OIN
5. Click on Go To Worksheet
23
Creating Basic Visualization In this module, you’ll learn about these concepts:
Getting Started in Tableau Desktop Elements of a visualization Formatting your view
24
Tableau Desktop Workspace Open Tableau from start menu of Windows. Start > All Programs > Tableau x.x By default, each time you open Tableau you will see the Start Page
Connection Options
Saved Workbooks Multiple Worksheet Saved Data Source (tds File)
F IGURE 17 – T ABLEAU S TART PAGE
F IGURE 18 - TABLEAU W ORKSPACE
25
F IGURE 19 - D ATA S HELF F IGURE 20 - D ATA T YPES
Tableau expresses fields and assigns data types automatically. If the data type is assigned by the data source, Tableau will use that data type. If the data source doesn’t specifically assign a data type, Tableau will assign one. Tableau supports the following data types: Text values Date values Numerical values Geographic values Boolean values
Tableau have an extra data types that you can define later in Tableau Desktop. Sets Hierarchy Group Bin
26
Tableau Desktop support many different aggregation types including Sum, Average, Median, Count, Count(Distinct), Minimum, Maximum, Std. Dev and Variance.
F IGURE 21 - A GGREGATION
To express data into visualization tableau use rows and column shelves. You can simply drag Dimension or Measure field into Columns / Rows and tableau will Show you the visualization.
F IGURE 22 - R OWS AND C OLUMNS S HELVES
27
Generated Fields Tableau generated fields are automatically added to the design window. Generated values include: Number of records is a calculated value that sums the rows in the data source. Note that field icons preceded by an equals sign are calculated values. Measure names and measure values are special fields that allow you to display multiple measures on a single axis. Longitude Latitude (generated) , If your data includes standard geographic fields like country, state, province, city, or postal codes Tableau will automatically generate the longitude and latitude values for the center points of each geographic entity displayed in your visualization
F IGURE 23 - G ENERATED F IELDS
Marks Card and Buttons Tableau applies color, shape, and size to visualizations using the view cards. The view cards also enable filtering, labeling, and provide a way for you to add details on demand that are not visible in your chart. Visual details are added to the chart by placing field pills on the desired mark type. Multiple fields can be placed on the color, label, detail, and tooltip buttons
Color—Expresses discrete or continuous values
Size—Expresses discrete or continuous values
Label—One or more fields can be expressed as label
Detail—Disaggregates the marks plotted
ToolTip/ToolTips—Makes fields available to ToolTips without disaggregating data
Shape—Expresses discrete or continuous fields
on marks
F IGURE 24 - M ARKS C ARD
28
Pages Shelf : Any field placed on the pages shelf generates an autoscrolling filter. Use it to create animated visualizations in Tableau Desktop.
F IGURE 25 - P AGE S HELF
Filter Shelf : Any field placed on the filter shelf enables a filter for that dimension or field. The style of filter control is dependent on whether the field is continuous or discrete. If you want to expose a filter in the worksheet, right-click on any pill used anywhere in the workspace and select the menu option Show Quick Filter.
F IGURE 26 - F ILTER S HELF
Using the Show Me button allows you to build visualizations very quickly. If you can decide on the combination of dimensions and measures you want to analyze, Show Me will build your visualization for you. It will place all of the pills on shelves automatically.
29
Analysis Via Show Me In this chapter, you will learn how to make a visualization via Show me. Show Me tells you what chart to use and why. It will also help you create complicated visualizations faster and with less effort. Show Me looks at the combination of measures and dimensions you’ve selected and interprets what chart types display the data most effectively. From the previous starter 1. Select Order Date and Sales 2. Click Show Me, it will expose the options available for that combination
Selecting combination of dimension and measures will highlight chart in the show me menu. Gray charts are not available.
F IGURE 27 - S ELECTING C HART
As you can see, Tableau marks lines(discrete) as recommendation denoted with a blue outline. At the bottom of show me area you also see additional details regarding requirements needed for building any available chart. The time series chart requires one date, one measure, and zero or more dimensions.
3. Click lines(discrete) chart on show me
F IGURE 28 - L INE (D ISCRETE )
4. Click + in year to show date hierarchy (quarter, month, day, hour, minutes, second)
30
F IGURE 29 - D ATE H IERARCHY
F IGURE 30 - A DD Q UARTER
31
Practice : Make your first view In this practice, you will show sales by product container, with this chart we can see which product container have the most sales. Format your view by adding profit into color and sales into label.
F IGURE 31 - PRACTICE FIRST V IEW
Step : From previous starter :
Double Click Product container and sales, then select show me, click horizontal bar Drag profit from measures to color marks Drag Sales from measures to label marks
32
Simplifying and Sorting Your Data In this module, you’ll learn about these concepts:
Data Filtering Sorting
33
Filtering Your Data There are a few different ways to add filtering to your visualization. Dragging any dimension or measure on to the filter shelf provides filtering that is accessible to the designer. Make that filter accessible to more people by turning it into a quick filter. This places it on the desktop where it is accessible to anyone—even those reading your report via Tableau Reader or Tableau Server. You can also create conditional filters that operate according to rules you define. Creating a Filter with the Filter Shelf 1. Drag Sales Measure into Column shelf and Product Category and Product Sub Category to Row shelf. 2. Dragging the subcategory field from the dimension shelf and placing it in the filer shelf exposes the filter menu. Notice that there are three other tabs on the filter menu. The Wildcard tab is typically used to search for text strings to filter. If you want to filter using another field that isn’t in your view you can use the Condition tab to select any field in your datasource and filter using that field. The Top tab facilitates building top and bottom filtering or filtering requiring other formula conditions. If you use more than one of the filtering options tabs to define your filter, Tableau applies the conditions defined in each tab in the order the tabs appear from left to right. General conditions will be applied first, then wildcard, then condition, and the top tab conditions last. Below the general field list to the right of the None button is a check box for the Exclude option. If Exclude is checked, the items that include check marks are filtered out of view. Exclude filters can take a little longer to execute than Include filters, especially if your data set is very large.
F IGURE 32 - F ILTER O PTION
34
If you want to make the filter available for people that are viewing the report via Tableau Reader or Tableau Server you need to expose the filter control on the desktop. To create a quick filter, point at and right click on any pill used on any shelf in your worksheet, then select the Show Quick Filter option.
F IGURE 33 - QUICK F ILTER
Bar Chart, Stacked Bar, Side By Side Bars These charts facilitate one-to-many comparisons. Bar charts are the most effective way to compare values across dimensions— their linear nature making precise comparisons easy. Stacked bar charts should not be used when there are many different dimensions because they can be overwhelming if too many colors are plotted in each bar. Side-by-side bars provide another way to compare measures across and dimensions on a single axis. Follow this step to create Bar Chart : 1. Click and Highlight Dimension Order Date, Product Category and Measure Sales and click Show Me, choose Horizontal Bar Chart.
F IGURE 34 - H ORIZONTAL B AR C HART
2. Click Swap in toolbar
35
F IGURE 35 - S WAP ROW AND COLUMN (H ORIZONTAL BAR )
3. Insert Order Date into Filters and choose Years > Next > Checklist Year 2011 and 2012.
F IGURE 36 - F ILTER
F IGURE 37 - Y EAR FILTER
36
F IGURE 38 - H ORIZONTAL B AR WITH FILTER
To Dynamically change your year in filter, right click on year in filter shelf and click Show Quick Filter
F IGURE 39 - S HOW Q UICK F ILTER
37
Follow this step to create Stacked Bar: 1. Click and Highlight Dimension Order Date, Product Category and Measure Sales and click Show Me, choose Stacked Bar.
F IGURE 40 - S TACKED BAR
2. Insert Order Date into Filters and choose Years > Next > Checklist Year 2011 and 2012.
F IGURE 41 - S TACKED BAR WITH FILTER
38
Follow this step to create Side by Side Bars: 1. Click and Highlight Dimension Order Date, Product Category and Measure Sales and click Show Me, choose Side by Side Bars.
F IGURE 42 - S IDE BY SIDE BAR
2. Insert Order Date into Filters and choose Years > Next > Checklist Year 2011 and 2012.
F IGURE 43 - S IDE BY SIDE BAR WITH FILTER
39
Sorting Data Tableau provides basic and advanced sorting methods that are easily accessed through icons or menus. Sorting isn’t limited to fields that are visible in the chart—any field in the datasource can be used for sorting. Manual Sorting via Icons The most basic way to sort is via the icons that appear in the toolbar menu. The toolbar menu sort icons provide ascending and descending sorts. Tableau also provides sorting icons near the headings and mark axis. If you don’t see an icon, hover your mouse near the area and it will appear. Notice the icon that appears in the sub-category pill on the row shelf? The light gray descending sort icon that appears in that pill provides an indication that a sort has been applied on that sub-category field. Clicking on the sort icon floating over the right-side of the sub-category heading provides ascending and descending sorts using the text of the product category headings. The sort icons that appear over and under the mark (bar) axis provide ascending and descending sorts based on the values displayed by the marks, and also add datasource order sorting.
F IGURE 44 - S ORT
Calculated Sorts using the Sort Menu More advanced sorting can be accessed by pointing at a dimension pill, right clicking, and selecting the Sort option. right-click on a dimension pill--in this example the Sub - Category pill. Tableau’s sort menu allows you to more precisely define the default sort method and order. The sort by section includes a drop-down menu that currently displays the sales field using an aggregation of sum. However, it is possible to select any field in the data set and change the aggregation. For example, you could also apply ascending sort by average profit. Leaving the sort menu open and using the apply button at the bottom right side of the menu is useful. You can apply a variety of sort options and see the result. When you decide to keep the sort, click the OK button.
40
F IGURE 45 - S ORT (2)
Sorting via Legends Another useful sort feature is enabled within legends. This Figure shows two versions of the same bar chart. The left view orders the blue delivery truck dimension on the bottom. The chart on the right shows regular air at the bottom. Reordering the position of the colors displayed within the color legend causes the order of the colors appearing in the bars to change. Reposition the colors within the color legend by pointing at a color, holding down the left mouse button, and dragging the color to the desired position
F IGURE 46 - S ORT V IA L EGEND
41
Organizing Your Data In this module, you’ll learn about these concepts:
Using Groups Creating and Using Hierarchies Creating a Combined Field Using Sets
42
Groups When you have a dimension that contains many members and your source data doesn’t include a hierarchy structure, grouping can provide summarized views of the data. You can manually group items from headers or multi-select marks in a chart. Tableau also provides a menu option with fuzzy search that will help you group by searching strings in large lists of values. You can even group by selecting marks in a view. If you need to work with data that isn’t structured the way you want it, grouping allows you to build that structure within Tableau. There are three ways to group headings. 1. click on the paper clip icon in the Tooltips that appears when you multi-select the headers. 2. Right click after selecting the headings and pick the group option in the menu. 3. One final option is available via the paper clip icon in the toolbar.
F IGURE 47 - G ROUPS
Hierarchies Hierarchies provide a way to start with a high-level overview of your data, and then drill down to lower levels of detail on demand. In this sample you can see a two-level view of the data that included product category and then subcategory. Follow this step to create hierarchies : 1. Right Click on Column Product Category Dimension Area > Create Hierarchies > Give Name : Product Hierarchies 2. Drag Column Product Sub Category under Product Category in Product Hierarchies.
43 F IGURE 48 - H IERARCHIES
Combined Field Combined fields to create a cross product of members from different dimensions. You would combine dimensions if you want to encode a data view using multiple dimensions. To combine the fields, select multiple dimensions in the Data window and then control-click the fields and select Combine Fields. For example, the selections shown below will produce a new field that consists of the Container and Customer Segment dimensions.
F IGURE 49 - C OMBINE F IELDS
The two dimensions are combined into a new dimension. The name of the field is automatically created from the names of the original fields. Control-click the new field and select Rename to change the name. When you use the new field in a view, a header is created for each combination of the two original dimensions. For example, the view below shows the members of the combined Customer Segment and Container fields.
FIGURE 50 - COMBINE FIELDS RESULT
44
Using Multiple Measure on the Same Axis In this module, you’ll learn about these concepts:
Comparing Views with Multiple Measure Using Measure Values and Measure Names Combo Charts Creating Dual Axis Charts Combined or Shared Axis Charts
45
Using Multiple Measure There are lots of different ways to compare multiple measures in a single view. For example, you can create individual axes for each measure or you can blend the two measures to share an axis and finally, you can add dual axes where there are two independent axes layered in the same pane. In any of these cases you can customize the marks for each axis to use multiple mark types and add different levels of detail. Views that have customized marks are called combination charts.
Individual Axis Add individual axes for each measure by dragging measures to the Rows and Columns shelves. Each measure on the Rows shelf adds an additional axis to the rows of the table. Each measure on the Columns shelf adds an additional axis to the columns of the table. For example, the view below shows quarterly sales and profit. The Sales and Profit axes are individual rows in the table and have independent scales.
F IGURE 51 - I NDIVIDUAL A XIS
Blended Axis Measures can share a single axis so that all the marks are shown in a single pane. Instead of adding rows and columns to the view, when you blend measures there is a single row or column and all of the values for each measure is shown along one continuous axis. For example, the view below shows quarterly sales and profit on a shared axis.
F IGURE 52 - B LEND A XIS
46
To blend multiple measures, simply drag one measure or axis and drop it onto an existing axis.
F IGURE 53 - B LEND A XIS (2)
Blending measures uses the Measure Names and Measure Values fields, which are generated fields that contain all of the measure names in your data source and all of the measure values. The shared axis is created using the Measure Values field. The Measure Names field is added to the Color shelf so that a line is drawn for each measure. Finally, the Measure Names field is filtered to only include the measures you want to blend.
Dual Axis You can compare multiple measures using dual axes, which are two independent axes that are layered on top of each other. Dual axes are useful when you have two measures that have different scales. To add the measure as dual axis drag the field to the right side of the view and drop it when you see a black dashed line. You can also select Dual Axis on the field menu for the measure.
F IGURE 54 - D UAL A XIS
The result is a dual axis view where the Profit axis corresponds to the purple line and the Shipping Cost axis corresponds to the brown line.
47
F IGURE 55 - D UAL A XIS (2)
You can add up to four layered axes: two on the Columns shelf and two on the Rows shelf.
Combination Charts When working with multiple measures in a view, you can customize the mark type for each distinct measure. For example, you can create a view with a line showing a target amount across several months and a bar chart showing the actual attainment for the months. These measures can be displayed as individual axes, blended axes, or dual axes. Because each measure can have customized marks, you can customize the level of detail, size, shape, and color encoding for each measure too.
F IGURE 56 - C OMBINATION C HART
To customize the marks for a measure: 1. Select the Marks card for the measure that you want to customize. There is a Marks card for each measure on the Rows and Columns shelves.
48
F IGURE 57 - C HANGE CHART IN COMBO CHART
2. Select a new mark type for the measure. Any changes to the mark type, shape, size, color, detail and other mark properties will be applied to the selected measure. For example, in the view below the SUM(Sales) Marks card is active. The Mark type has been changed to Bar and when Department is placed on Color, the encoding and level of detail is only applied to the SUM(Sales) marks. The SUM(Sales Plan) is not broken down by Department.
F IGURE 58 - C OMBO C HART
Select the All Marks card to modify properties for all measures at once.
49
Practice : Dual Axis with Combine Chart
F IGURE 59 - PRACTICE D UAL A XIS WITH C OMBINE C HART
Follow this step to create Dual Axis with Combination Chart: 1. Click and Highlight Dimension Order Date ,Measure Sales and Profit click Show Me, choose Dual Lines. 2. Default time series for Dual Lines is discreate, to change into continues series, You can click on Year(Order Date) in coloumn shelf and choose Year above the default. 3. Click on SUM(Profit) marks and change the chart to Bar
Practice : Multiple measure in 1 Axis Now you will develop an intricate view of three aggregations for same data item, profit.
F IGURE 60 - PRACTICE M ULTIPLE MEASURE IN 1 A XIS
50
Step
Create a new worksheet using the coffee chain starter. Duplicate profit three items by right clicking on it directly in the measures pane and selecting duplicate. Change the aggregation of the first copy to average (AVG) by right clicking on it, selecting Default properties Aggregation Average, and renaming it Profit Avg. Change the second copy to Maximum(MAX) and name it Profit Max. Change the third copy to Minimum(MIN) and name it Profit Min. Drag Market and Product Type on the Column Shelf On the marks card, change the dropdown from automatic to shape Drag profit Avg to Rows Drag Profit Max and Profit Min into the profit Avg Axis
Change the shape by click on shape in Marks card
Drag Measure Names to the color in marks card
51
Change the color by click color on the marks card then click apply or OK after you change the color
52
Mapping Data Geographically In this module, you’ll learn about these concepts:
Mapping in Tableau Geographic Mapping
53
Maps (Symbol and Filled) Selecting a field with a small globe icon makes maps available in Show Me. Symbol maps are most effective for displaying very granular details, or if you need to show multiple members of a small dimension set. In Filled maps it is a good idea to make the marks more transparent and add dark borders because marks tend to cluster around highly populated areas. Using the color button on the marks card to do this makes the individual marks easier to see. The color and size legends in view are automatically provided by Tableau. Filled maps display a single measure using color within a geographic shape. If you restrict filled maps to smaller geographic areas (state, province) they effectively display more granular areas like county or postal code. Follow this step to create Symbol Map : 1. Choose Dimension State or Province and click Show Me, tableau will automatically highlight symbol maps.
F IGURE 61 - S YMBOL M APS
2. Insert Sales Measure into Size and color marks.
F IGURE 62 - A DD SIZE AND COLOR ( SYMBOL MAPS )
Follow this step to create Filled Map :
54
1. Choose Dimension State or Province and click Show Me, choose Filled Maps.
F IGURE 63 - B ASIC FILLED M AP
2. Drag Drop Sales Measure into Color marks.
F IGURE 64 - A DD COLOR (F ILLED M AP )
55
Viewing Specific Values and Distributions In this module, you’ll learn about these concepts:
Creating Crosstab Creating Highlight Tables Grand Totals, Sub-totals and changing Aggregation Bins and Histogram
56
Text Tables Crosstab
F IGURE 65 - B ASIC T EXT TABLES
Text tables look like grids of numbers in a spreadsheet. Crosstabs are useful for looking up values. The text table on the below has been enhanced by adding a Profit to color , so we can see which sales with high profit and low profit.
F IGURE 66 - C OLOR ADD IN T EXT T ABLE
Highlight Tables Comparing granular combinations of dimensions and measures can be done effectively with each of these charts. Highlight tables can display one measure using a color gradient background to differentiate values.
57
Follow this step to create Highlight Tables : Click and Highlight Dimension Region, Product Category, Product Sub Category and Measure Sales and click Show Me, choose Highlight Table. Tableau will automatically highlight the Sales Measure from the largest and smallest values of sales based on color.
F IGURE 67 - H IGHLIGHT T ABLES
Grand Totals and Aggregation You can calculate grand totals by selecting one of the Grand Totals options on the Analysis > Totals menu. The grand totals are added as an additional row or column to your table.
The following rules dictate whether you can turn on grand totals:
58
The view must have at least one header – Headers are displayed whenever you place a dimension on the Columns shelf or the Rows shelf. If column headers are displayed, you can calculate grand totals for columns. If row headers are displayed, you can calculate grand totals for rows.
Measures must be aggregated – The aggregation determines the values displayed for the totals.
Grand Totals cannot be applied to continuous dimensions.
You can also display totals for graphical views of data. In the figure below, only column totals are calculated because the table contains only column headers.
Grand Total Aggregation When grand totals are turned on in the view (either Row grand totals or Column grand totals), you can specify how totals should be computed. To configure grand totals, from the Analysis menu choose Totals > Total All Using to display a submenu:
When you choose Current Aggregation from the submenu, totals are computed as they always were in earlier versions of Tableau: with column and row totals based on the configured aggregation for the field. In this case, computations are based on the underlying data, which is disaggregated, and not on the data in the view. When you choose any of the other values (Sum, Average, Minimum, or Maximum), all totals are computed using the selected aggregation. The computations are performed on the aggregated data you see in the view. The following table summarizes the standard aggregations and the grand totals that are calculated by default when, from the Analysis menu, Totals > Total All Using is set to the default value Automatic. 59
Aggregation
Calculation Description
Sum
Shows the sum of the values shown in the row or column.
Average
Shows the average of the values shown in the row or column.
Median
Shows the median for the values shown in the row or column.
Count; Shows how many values or distinct values are displayed in the rows and columns in the Count Distinct view. Minimum
Shows the minimum value shown in the row or column.
Maximum
Shows the maximum value shown in the row or column.
Percentile
Shows the average percentile for all values shown in the row or column.
Standard Deviation
The grand total using standard deviation is the standard deviation of the values shown in the row or column.
Variance
The grand total using variance is not the variance of the rows and columns in which they reside but rather of the underlying data behind the row or column.
Customizing Your Data In this module, you’ll learn about these concepts:
Calculation Type Creating Calculated Tables Using Logic Statements Type Conversions and Date Calculations Using Quick Table Calculations
60
61
Calculated Fields Calculated Fields—power to answer your difficult questions Sometimes your analysis needs a data item that your original data source does not include, but that you could calculate using the current data items. This is called a calculated field. For example, you might need a new data item called Profit Ratio, the ratio of the profit field to the sales field. Another example would be the creation of a conditional statement called “Shipping Commitment Met” that determines if the actual time to ship was greater than the promised time to ship, returning a value of 1 if true or 0 if false.
Calculated Values require that you enter fields, functions, and operators. Tableau strives to make formula creation fast and easy, so it is possible to write formulas with minimal typing. Once you’ve connected to a datasource, you can create a calculated field from the main menu by selecting Analysis/Create Calculated Field. This example uses the Superstore spreadsheet. Figure 4-4 shows the Calculated Value editing window. The figure shows a calculation for Profit Ratio that uses two fields from the Superstore file to derive the result. The Name field at the top of picture below is where you type the name of your Calculated Value as you want it to appear.
62
F IGURE 68 - C ALCULATION F IELD W INDOW
in the data window of the worksheet. The Formula box is used to write the script for the formula. You will also see that Tableau color-encodes different elements of formulas so that they are easy to separate visually. Fields are orange, Parameters are purple, and Functions are blue. Notice the example in picture includes comments at the top, color-encoded in green. Comments are useful for documenting sections of complex formulas or for adding basic descriptive information to other analysts that may use your formula in their work. You can add comments anywhere in the formula window by typing two forward slashes (//) in front of the text.
Practice : Calculation Profit Ratio
F IGURE 69 - PRACTICE CALCULATION
From previous starter, add new sheet and create the calculation Step :
Right Click on any dimension / measure (it best to select the measure you want to make a calculation) Select Create Calculated Fields Input the formula of Profit Ratio Click ok after you finished Double Click Region 63
Double Click Profit Ratio and you got the number, but you have to change the number format from standard number to percentage
Right Click on Profit Ratio fields Select default properties -> number format
Select Percentage to be Default Number Format(Profit Ratio)
64
Logical Statement
Case Use the CASE function to perform logical tests and return appropriate values. CASE is often easier to use than IIF or IF THEN ELSE. The CASE function evaluates expression, compares it to a sequence of values, value1, value2, etc., and returns a result. When a value that matches expression is encountered, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, then Null is returned. Typically, you use an IF function to perform a sequence of arbitrary tests, and you use a CASE function to search for a match to an expression. But a CASE function can always be rewritten as an IF function , although the CASE function will generally be more concise. Many times you can use a group to get the same results as a complicated case function. Examples CASE [Region] WHEN "West" THEN 1 WHEN "East" THEN 2 ELSE 3 END CASE LEFT(DATENAME('weekday',[Order Date]),3) WHEN "Sun" THEN 0 WHEN "Mon" THEN 1 WHEN "Tue" THEN 2 WHEN "Wed" THEN 3 WHEN "Thu" THEN 4 WHEN "Fri" THEN 5 WHEN "Sat" THEN 6 END
If you need to include numeric comparisons in your conditions, use a nested IF clause. The CASE function compares strings only. For example, suppose you want to break the values of the Sales field into three custom categories: one for sales less than 200, one for sales between 200 and 300, and one for sales between 300 and 400. The formula would be: IF [Sales] < 200 THEN "Low" ELSEIF [Sales] < 300 THEN "Medium" ELSEIF [Sales] < 400 THEN "High" ELSE "NULL" END
IIF Examples IIF(7>5, "Seven is greater than five", "Seven is less than five") IIF([Cost]>[Budget Cost], "Over Budget", "Under Budget") IIF([Budget Sales]!=0,[Sales]/[Budget Sales],0) IIF(Sales>=[Budget Sales], "Over Cost Budget and Over Sales Budget", "Over Cost Budget and Under Sales Budget","Under Cost Budget")
IF Then End / If then else end Examples IF [Cost]>[Budget Cost] THEN "Over Budget" ELSE "Under Budget" END IF [Budget Sales]!=0 THEN [Sales]/[Budget Sales] END IF [Region]="West" THEN 1 ELSEIF [Region]="East" THEN 2 ELSE 3 END
Isdate The ISDATE function returns TRUE if the string argument can be converted to a date and FALSE if it cannot. Examples ISDATE("January 1, 2003") = TRUE ISDATE("Jan 1 2003") = TRUE ISDATE("1/1/03") = TRUE ISDATE("Janxx 1 2003") = FALSE
65
Practice : Is Profitable?
F IGURE 70 - PRACTICE I S PROFITABLE ?
Find state that profitable and unprofitable. Step :
Create Calculated Field , change the name of calculated field to Isprofitable? , and input the formula of calculation if (sum([Profit]) > 0) then 'Profitable' else 'Unprofitable' end
After that, double click state and isprofitable
66
Drag profit into label marks to add the label
Type Conversion The result of any expression in a calculation can be converted to a specific data type. The conversion functions are STR(), DATE(), DATETIME(),INT(), and FLOAT(). For example, if you want to cast a floating point number like 3.14 as an integer, you could write INT(3.14). The result would be3, which is an integer. The casting functions are described below. Examples DATE("April 15, 2004") = #April 15, 2004# DATE("4/15/2004") DATE(#2006-06-15 14:52#) = #2006-06-15# DATETIME(“April 15, 2005 07:59:00”) = April 15, 2005 07:59:00 FLOAT(3) = 3.000 INT(8.0/3.0) = 2 INT(4.0/1.5) = 2
Quick Table Calculations You can add common table calculations to your view using the Quick Table Calculations menu item on the field context menus. These quick calculations are predefined table calculations based on the most common scenarios. To add a quick table calculation: 1. Control-click the measure you want to use in the table calculation and select Quick Table Calculation. 2. On the sub-menu select one of the following options: o
Running Total
o
Difference 67
o
Percent Difference
o
Percent of Total
o
Rank
o
Percentile
o
Moving Average
o
Year to Date (YTD) Total
o
Compound Growth Rate
o
Year over Year Growth
o
Year to Date (YTD) Growth
After adding a quick table calculation to the view, you can edit its definition by selecting Edit Table Calculation from the field’s context menu. Table calculations are a special type of calculated field that computes on the local data in Tableau. While you can use the built-in table calculations such as Percent of Total, Difference From, Running Total, and so on; the functions required to define these calculations are also available for use in your own custom calculated fields. Customizing table calculations allows you to compute values such as the difference in number of orders this quarter versus an average quarter, total sales for regions that have above average margin, time since first click on a website, average temperature based on the last three days weighted at 10%, 40%, and 50%, and so much more. An easy way to become familiar with the Table Calculation functions is to add a basic table calculation and then click the Customize button in the lower left corner of the Table Calculation dialog box.
F IGURE 71 - QUICK TABLE C ALCULATION
When you click Customize, the Calculated Field dialog box opens showing the formula for the calculation. You can see that it uses special functions.
68
After you customize the calculated field, the changes are not saved until you click OK in the Calculated Field dialog box and in the Table Calculation dialog box. The new table calculation field is added to the Data window.
When you use that field in other views, it uses the default addressing and partitioning specified when the table calculation was created. You can change the addressing by control-clicking the field and selecting an option from the Compare To context menu.
69
Making Your View Available In this module, you’ll learn about these concepts:
Building Dashboard Dashboard Actions
70
Dashboard 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. For example, you may have a set of views that you review every day. Rather than flipping through each worksheet, you can create a dashboard that displays all the views at once. Similar to worksheets, dashboards are shown as tabs at the bottom of the workbook and update with the most recent data from the data source. When you create a dashboard, you can add views from any worksheet in the workbook. You can also add a variety of supporting objects such as text areas, web pages, and images. From the dashboard, you can format, annotate, drill-down, edit axes, and more. Each view you add to the dashboard is connected to its corresponding worksheet. That means when you modify the worksheet, the dashboard is updated and when you modify the view in the dashboard, the worksheet is updated. Creating Dashboards You can create a dashboard in much the same way you create a new worksheet. After you create a dashboard you can add and remove views and objects. Select Dashboard > New Dashboard.
F IGURE 72 - C REATE D ASHBOARD
ADDING DASHBOARD OBJECTS Dashboards are used to monitor and analyze a collection of related views and information. A dashboard object is an area in the dashboard that can contain supporting information that is not a Tableau view. For example, you can add a text area to include a detailed description. Or you may want to add a web page that is the destination of your hyperlinks. Dashboard objects are listed at the bottom of the dashboard window. You can add text, images, web pages, and blank areas. To add a dashboard object: Click and drag a dashboard object and drop it on the dashboard.
71
DASHBOARD WORKSPACE
F IGURE 73 - D ASHBOARD W ORKSPACE
ADDING VIEWS TO A DASHBOARD When you open a dashboard the Dashboard window replaces the Data window on the left side of the workbook. The Dashboard window lists the worksheets that are currently in the workbook. As you create new worksheets, the Dashboard window updates so you always have all worksheets available when adding to a dashboard.
F IGURE 74 - A DD V IEW TO D ASHBOARD
To add a view to a dashboard: Click and drag a worksheet from the Dashboard window to the dashboard on the right.
72
F IGURE 75 - D ASHBOARD
Actions Tableau allows you to add context and interactivity to your data using actions. Link to web pages, files, and other Tableau worksheets directly from your analytical results. Use the data in one view to filter data in another as you create guided analytical stories. Finally, call attention to specific results using highlighting. For example, in a dashboard showing home sales by neighborhood you could use actions to help you quickly see relevant information for a selected neighborhood. Select a neighborhood in one view which then highlights the related houses in a map view, filters a list of the houses sold, and opens a web page showing census data for the neighborhood. There are three kinds of actions in Tableau: Filter, Highlight, and URL actions.
Filter Actions Filter actions are a way to send information between worksheets. Typically a filter action is used to send information from a selected mark to another sheet showing related information. For example, when looking at a view showing the sales price of houses, you may want to be able to select a particular house and show all comparable houses in a different view. You could define a filter action to accomplish this task. First you need to decide what comparable means. In this case, say that comparable houses are houses with a similar sale price and square footage. A filter action to show comparable houses can be defined by selecting a destination worksheet and defining filters on sales price and square footage. Filter actions work by sending the data values of the relevant source fields as filters to the destination sheet. If you launch the filter action described in this example from a house that sold for $450,000, the destination sheet will have a filter to only show houses that sold for the same amount. 1. On a worksheet, select Worksheet > Actions. From a dashboard, select Dashboard > Actions. 2. In the Actions dialog box, click Add Action and then select Filter.
73
F IGURE 76 - A CTION FILTER
3. In the subsequent dialog box specify a name for the Action. Use a name that defines the action. If you choose to run the action using the menu the name is the option that shows on the menu. For example, when sending housing information from one sheet to a map, the name could be “Map all comparable houses sold in February” You can use variables in the name that will be filled in based on the values of the selected field.
4. Use the drop-down list to select a source sheet or data source. When you select a data source or dashboard sheet you can further refine by selecting the individual sheets you want to launch the action from.
5. Then select how you want to launch the action. Select one of the following options: o
Hover - rest the pointer over a mark in the view to run the action. This option works well for highlight and filter actions within a dashboard. 74
o
Select - click on a mark in the view to run the action. This option works well for all types of actions.
o
Menu -control-click a selected mark in the view and then select an option on a the context menu. This option works well for filter and URL actions.
F IGURE 77 - RUN A CTION ON
6. Use the second drop-down list to select a target sheet. When you select a dashboard sheet you can further refine the target by selecting one or more sheets within the dashboard.
7. Specify what to do when the select is cleared in the view. You can select from the following options: o
Leave the filter - leaves the filter on the target sheets. The target views in the dashboard will show the filtered results.
o
Show all values - changes the filter to include all values.
o
Exclude all values - changes the filter to exclude all values. This option is useful when you are building dashboards that only show some sheets if a value in another sheet is selected.
8. Setup one or more filters to specify the data that you want to show on the target sheets. You can filter on All Fields or define filters on Selected Fields. 9. If you are defining filters for specific fields click Add Filter.
75
10. In the Add Filter dialog box, select a source and target data sources and fields. When you run the action from a specific mark on the source sheet, a filter is added to the target sheet that only includes values for the target field that match the values of the source field. In the comparable houses sheet link example, the Source Field is Beds and the Target Field is Beds. That means when you launch the sheet link for a house that has three bedrooms, the destination worksheet will only show houses that also have three bedrooms.
11. When finished, click OK three times to close all open dialog boxes and return to the view. If you are connected to a relational data source, you can add sheet links across data sources even if the field names are not exactly the same. One data source may have a field titled Latitude while another has a Lat field. Using the drop down lists in this dialog box, you can associate the Latitude field to the Lat field. When using a multidimensional data source, the destination sheet must use the same data source as the source sheet. Moreover, the source field names must match the destination field names. In Tableau, multidimensional data sources are supported only in Windows.
Highlight Actions Highlight actions allow you to call attention to marks of interest by coloring select marks and dimming all others. You can highlight marks in the view by selecting the marks you want to highlight, use the color legend to select related marks, or create an advanced highlight action. Color legend highlighting is a powerful analytical mode for the color legend that allows you to focus on select members in the view. When you turn on color legend highlighting the marks associated with the selected items in the color legend are colored while all other marks are gray. For example, the views below show the relationship between order quantity and profit for several products. The view on the left uses the normal color legend, all marks are colored based on their shipping mode. The view on the right uses legend highlighting to call out the products that were delivered via Delivery Truck.
76
Normal Color Legend
Color Legend Highlighting Enabled
You can easily switch between legend highlighting and normal modes using the color legend card menu. Then, if you like how a view is highlighted, you can assign the highlight colors to the color palette. The old colors are replaced with the highlight colors. To turn on color legend highlighting: 1. Click the Highlight button the color legend card menu.
at the top of the color legend or select Highlight Selected Items on
F IGURE 78 - H IGHLIGHT B UTTON
2. Select an item in the color legend. Once legend highlighting is turned on, you can quickly focus on specific data in the view by selecting different items in the color legend. When color legend highlighting is turned on a Highlight Action is created and can be modified in the Actions dialog box. To turn off color legend highlighting:
Click the Highlight button the color legend card menu.
at the top of the color legend or select Highlight Selected Items on
When you turn color legend highlighting off the action is removed from the Actions dialog box. 77
If you like how the view is highlighted and want to keep a specific member highlighted even when you turn off legend highlight mode, you can assign the highlight colors to the existing color palette. The original color legend is discarded and the highlight colors become the new color palette for the legend. To assign the highlight colors to the color palette, select Assign Highlight Colors to Palette on the color legend card menu.
Another way to add a highlight action is using the highlight button in the toolbar.Similar to the color legend highlighting, the toolbar button lets you highlight a collection of related marks in the view. To turn on highlighting, select the fields you want to use for highlighting on the toolbar menu. Then select a mark in the view to see the related data.
For example, the view below shows sales vs. profit by region. When a mark is selected, all other marks from that region that were shipped using the selected ship mode are highlighted. In this case you can quickly see all products from the Western region that were shipped via Delivery Truck.
78
The toolbar menu also lets you highlight on All Fields or Dates and Times. All Fields will consider all fields when determining matching records; Dates and Times considers all date and time fields. When you use the Highlight toolbar button an action is created in the Actions dialog box. You can modify the action to create more advanced highlighting behavior. Finally, you can use the toolbar button to disable highlighting across the entire workbook or for just the active sheet.
You can define more advanced highlight actions using the Actions dialog. There you can specify source and target sheets along and the fields you want to use for highlighting. Follow the steps below to create a Highlight Action. To create a highlight action: 1. On a worksheet select Worksheet > Actions. From a dashboard, select Dashboard > Actions. 2. In the Actions dialog box click the Add Action button and then select Highlight.
F IGURE 79 - A CTION H IGHLIGHT
3. Give the action a name that will identify it in the Actions dialog. Try to make it descriptive. For example, Highlight Products Shipped by Delivery Truck. You can use variables in the name that will be filled in based on the values of the selected field.
79
4. Use the drop-down list to select the Source sheet or data source. If you select a data source or a dashboard sheet you can further select individual sheets within them.
5. Select how you want to launch the action. You can select from the following options: o
Hover - rest the pointer over a mark in the view to run the action. This option works well for highlight and filter actions within a dashboard.
o
Select - click on a mark in the view to run the action. This option works well for all types of actions.
o
Menu - control-click a selected mark in the view and then select an option on a the context menu. This option works well for filter and URL actions.
6. Select a Target sheet. If you select a dashboard you can further select individual sheets within the dashboard.
7. Select the fields you want to use for highlighting. Select from the following options:
80
o
Selected Fields - marks in the target sheet are highlighted based on select fields. For example, highlighting using the Ship Mode field will result in an action that highlights all marks in the target sheet that have the same ship mode as the selected mark in the source sheet.
o
Dates and Times - marks in the target sheet are highlighted when their date and time match those of the marks selected in the source sheet. All dates and time fields are considered when determining a match.
o
All Fields - marks in the target sheet are highlighted when they match the marks selected in the source sheet. All fields are considered when determining a match.
8. When finished, click OK twice to close the dialog boxes and return to the view.
URL Actions A URL action is a hyperlink that points to a Web page, file, or other web-based resource outside of Tableau. You can use URL actions to link to more information about your data that may be hosted outside of your data source. To make the link relevant to your data, you can substitute field values of a selection into the URL as parameters. To add a hyperlink: 1. On a worksheet, select Worksheet > Actions. From a dashboard, select Dashboard >Actions. 2. In the Actions dialog box, click Add Action and then select URL.
F IGURE 80 - URL A CTIONS
3. In the subsequent dialog box, specify a name for the link.
81
Make the name descriptive of the action. If you choose to run the action using the menu, the name you specify here becomes the option that shows on the menu. For example, when linking to more product details, the name could be “Show More Details.” You can use variables in the name, which will be filled in based on the values of the selected field.
4. Use the drop-down list to select a source sheet or data source. If you select a data source or dashboard you can select individual sheets within it.
5. Select the fields you want to use for highlighting. Select from the following options: o
Hover - rest the pointer over a mark in the view to run the action. This option works well for highlight and filter actions within a dashboard.
o
Select - click on a mark in the view to run the action. This option works well for all types of actions.
o
Menu - control-click a selected mark in the view and then select an option on a the context menu. This option works well for filter and URL actions.
6. Specify the URL. You can use any URL that your browser can recognize including web pages, FTP resources, and files. Just as you can use variables in the name of the URL, you can also use field values and filter values as parameters in the URL. That means that you can send information about each selected mark or filter setting to a given website. In the URL you specify, include the appropriate prefix to ensure that the resulting hyperlink directs to the correct location. For example, if your URL links to a web page, include the http:// prefix. When using a URL action to point to an external file (rather than a web page), use the full universal naming convention (UNC) path for the URL action. A UNC path is the full path of a resource or file that is stored on the machine, which includes the full computer name, drive, path 82
to the file, and file name. For example, for a text file D:\myfile.txt, specify the following UNC path: \\workstation1\d$\myfile.txt, where "workstation1" is the computer name in your company domain. 7. (Optional) Select any of the following options: o
URL Encode Data Values - select this option if your data contains values that use characters that are not allowable in a URL. For example if one of your data values contains an ampersand, such as “Sales & Finance,” the ampersand must be translated into characters that your browser understands (URL encoded) if you want to include that value in the URL.
o
Allow Multiple Values - select this option if you are linking to a webpage that can take lists of values as parameters in the link. For example, say you select several products in a view and you want to see each product’s details hosted on a webpage. If the server can load multiple product details based on a list of identifiers (product ID or product name), you could use multi-select to send the list of identifiers as parameters. When you allow multiple values, you must also define the item delimiter, which is the character that separates each item in the list (for example, a comma). You must also define the Delimiter Escape, which is used if the delimiter character is used in a data value.
8. When finished, click OK twice to close the dialog boxes and return to the view.
83
Practice : Dashboard Sales Dashboard Audience : VP of Sales at a coffee chain Overall Objective of Dashboard : Sales Updates for Yearly Reviews by sales VP The Sales VP has a question :
What are sales by state? What are sales by product type? What are sales trend by product type?
Result
84
Sharing Your Work In this module, you’ll learn about these concepts:
Export to an Image File Exporting the Data Only Other Sharing Options
85
Export Export as image Copy to another application To insert an image of your Tableau results into another application such as PowerPoint, Word, or Excel, follow these steps: 1. Select Worksheet > Copy > Image. 2. In the Copy Image dialog box, select the contents you want to include in the image and the legend layout (if the view contains a legend). 3. Click Copy. Tableau copies the current data view to the Clipboard. 4. Open the target application paste from the Clipboard. If the target application offers the option, paste the images as an Enhanced Meta File to get the best presentation quality.
Export to an image The export image command saves the current view as an image file. You can export to an image file with the following three steps. 1. Select Worksheet > Export > Image. 2. In the Copy Image dialog box, select the contents you want to include in the image and the legend layout (if the view contains a legend). 3. Click Save. 4. In the Save Image dialog box, navigate to where you want to save the image file and type a file name into the text box. Select a file format from the Save as type drop-down menu. 5. Click Save.
Print to pdf You can publish one or more views to PDF by selecting File > Print to PDF. When printing a sheet, quick filters are not included. To show quick filters, create a dashboard containing the sheet and print the dashboard to PDF. Note, when printing a dashboard to PDF, the contents of web page objects are not included.
Export as Data If you want to export data from Tableau to another application, or create a new data source that contains a portion of the records in your original data source, there are several options in Tableau. When exporting data, you should keep these rules in mind: 86
You can select any portion of a data view to export. If you want to export all data in a view, control-click in the view and choose Select All. Copying and exporting to a crosstab always exports all data in the view regardless of what you have selected.
The fields that are exported to the new data source come from the fields on the worksheet shelves. The exception is fields that are external filters--that is, fields that appear only on the Filters shelf.
If you want to include other fields (either dimensions or measures) with the exported data without changing the basic view, place those fields on the Level of Detail shelf.
Copy Records to Clipboard Typically this function is used to copy records from Tableau into Microsoft Excel. To create an Excel spreadsheet from Tableau data, follow these three steps: 1. Select
the
desired
data
in
Tableau.
Control-click the view and click Select All.
F IGURE 81 - S ELECT C OPY T O C LIPBOARD
2. Select Worksheet > Copy > Data, or control-click the view and select Copy > Data from the context menu. 3. Open an Excel worksheet and paste the data into a new sheet. Notice that the fields placed on the Rows, Columns, and Color shelves are copied into the sheet. However, the Customer Segment field is not copied because it is an external filter (it appears only on the Filters shelf).
87
Export Crosstab to Excel There is a more direct way to transfer a cross-tab view of data to Microsoft Excel. Select Worksheet > Export > Crosstab to Excel. Tableau automatically pastes a crosstab version of the current view into a new Excel workbook. This option automatically opens a new instance of the Excel application. Although, copying a crosstab to Excel is more direct, it can decrease performance because it is copying the formatting as well as the data. If the view you are exporting contains a lot of data, a dialog box opens asking whether you want to copy the formatting options. Disregarding the format can enhance performance.
Exporting Data Source At any time while connected to a data source, you can export data source information as a shortcut that allows you to quickly connect to the data source in the future. You might want to do this if you often connect to the same data source multiple times or if you’ve added joined tables, default properties, or custom fields, such as groups, sets, calculated fields, and binned fields, to the Data window. Note: You can also save custom fields by saving the workbook or by creating a bookmark file. You can export the data source in one of the following two formats: Data Source (.tds) - contains just the information needed to connect to the data sources such as data source type, location, and custom fields. If you connect to local file data sources (Excel,text, and extracts), the file path is stored in the data source file. Data source files contain the following types of information:
data source type
data source connection information specified in the data source page (e.g., server, port, location of local files, tables, and so on)
groups
sets
calculated fields
bins
default field properties (e.g., number formats, aggregation, sort order, etc.)
Packaged Data Source (.tdsx) - contains all the information in the Data Source (.tds) file as well as any local file data sources (Excel, text, and extracts). This file type is a single zipped file and is good for sharing a data source with people who may not have access to the original data that is stored locally on your computer.
88
After you export, the data sources are available on the Data page. By default, the data source files are stored in the Datasources folder of the Tableau Repository. Data source files stored in another location do not display on the Data page. You can connect to data source files by selecting File > Open and navigating to the file. You can also connect by dragging the data source file onto Tableau Desktop icon or onto the running application. Note: If you move a local file data source that is referenced by a .tds file, you will be prompted to locate or replace the original data source when you try to open the data source file. To avoid saving a specific file path, save the data source as a TDSX file, which packages a copy of the original local file data source with the .tds file. If you choose to replace the original data source, the replacement data source must be of the same type (Excel, MySQL, and so on) as the original.
To export a data source 1. On the Data menu, select a data source, and then select Add to Saved Data Sources. 2. Complete the Add to Saved Data Sources dialog box by specifying a file name and selecting the type of data source file.
The new .tds or .tdsx file is listed in the Saved data sources section of the Data page.
89
Thank You You can reach us at: PT CYBERTREND INTRABUANA JL. Melati No. 10 Ragunan, Jakarta Selatan Phone
:
+62 21 7822 471-2
Fax
:
+62 21 7822 473
Web
:
www.cybertrend-intra.com
90
