Category: Tableau

Tableau User Experience Summary

Since our office got us both the Tableau and Power BI license, it is up to us to incorporate these data visualization tools in our analytical work. It takes a while to figure out how to perform particular tasks in both Tableau and Power BI, though the learning curve for Tableau is steeper, but once you figure out a way to accomplish what you suppose to do with the software, the efficiency and the output are quite amazing.

The Good

  • Perfect for presentation. Able to define the Size of the Dashboard to optimize the presentation results. For example, we can define the dashboard size to 1550 x768 for the wide screen presentation.
  • Easy to define data hierarchy. Once defined, the list result of the fields in the low hierarchy in the Filters pane will just show the filtered results determined by the fields in the higher hierarchy in the Filters pane. Details see example: Hierarchical Filter.
  • Abundant data connections. Able to link directly to statistical files, including SAS, SPSS, and R. Especially SAS dataset is the basic type of data that we maintain and update for all the reporting function. Tableau can also read different type of spatial files which is super convenient. Details see example of loading Census Shape Data.
  • Tableau prep can recognize tables in the PDF documents and combine different PDF tables into one data table. Details see example: Extract and Union Join Tables in PDF.

The Bad

  • The Individual visual has to be set up in each Worksheet tab first and then combined into Dashboard tab. It is kind of inefficient in terms of using the Worksheet/Dashboard/Story tab spaces, comparing to Power BI that you can directly create different visuals in one canvas.
  • Not able to union the datasets from multiple data sources in Tableau Desktop. You can only union datasheets in the same workbook, or files with similar names under same folder. The tables need to have the same number of fields with same field names and data type.
  • The name for the data connection doesn’t automatically update when you change the data source to a different spreadsheet. Have to use “Rename …” to manually change the name of the data connection.

The In-between

  • Sometimes it is not easy to understand what certain menu items are doing to the visualization, or where to click to change a particular aspect of the visual. eg. font size for title, label and legend; remove lines on the visual; show/hide title, label, legend, and axis etc.

The Particular

  • Ratio calculation: Need to understand the LOD expression to properly use the Fixed function and Include function. See example for ratio calculation using Fixed function.
  • Alias:
  • Set:
  • Entire View:
  • Size vs Angle:
  • Dual Axis:

Tableau: Load Census Shape Data

A. Download data from web

  • 2016 Census Boundary File
  • select ArcGIS (.shp)
  • select census subdivisions and continue to download
  • unzip file to local directory

B. Tableau

  • Data-> New Data Source ->Spatial File-> Open the .shp file
  • Open a new sheet, drag Geometry from Measures to the canvas
  • drag Ccsname over the Colour Property in Marks card
Coloured by census consolidated subdivision name

C. Attribute

  • FID: specific to ArcGIS
  • Shape: specific to ArcGIS
  • CCSUID: Uniquely identifies a census consolidated subdivision (2-digit province/territory + 2-digit census division code +3-digit census consolidated subdivision code)
  • CSDUID: Uniquely identifies a census subdivision (2-digit province/territory + 2-digit census division code +3-digit census subdivision code)
  • CCSNAME: Census consolidated subdivision name.
  • CSDNAME: Census subdivision name.
  • PRUID: Uniquely identifies a province or territory.
  • PRNAME: Province or territory name.
  • CDUID: Uniquely identifies a census division.
  • CDNAME: Census division name.
  • CDTYPE: Census division type.
  • CSDTYPE: Census subdivision type.
  • ERUID: Uniquely identifies an economic region ( 2-digit province/territory + 2-digit economic region code
  • ERNAME: Economic region name.
  • SACCODE: 3-digit statistical area classification code
  • SACTYPE: The statistical area classification groups census subdivisions according to whether they are a component of a census metropolitan area, a census agglomeration, a census metropolitan influenced zone or the territories.
  • CMAUID: Uniquely identifies a census metropolitan area/census agglomeration.
  • CMAPUID: Uniquely identifies the provincial/territorial part of a census metropolitan area/census agglomeration.
  • CMANAME: Census metropolitan area/census agglomeration name.
  • CMATYPE: Identify whether the unit is a census metropolitan area, a tracted census agglomeration or a non-tracted census agglomeration.

Tableau: Show Table with Row Level Records with No Aggregation

A. Use Hyper Tableau Data Extracts

  • Follow post to create Hyper file

B. Worksheet configuration

  • Create new measure DISTINCTID
  • Analysis Menu: Aggregated Measures -> Uncheck
  • Analysis Menu: Stack Marks -> Off
  • Drag Measure Names from Dimension to Filters Shelf -> Measure Names will show in the Filter Shelf -> Edit Filter -> To only show Effect Size and N.
  • Drag Measure Values from Measures over Text on Mark Shelf -> Measure Values Shelf will appear with Effect Size and N
  • Drag Measure Names to Column Shelf
  • Drag DISTINCTID, Study, Grade range, Languages (bilingual) and Dominant cognitive benefits measured to Row Shelf
  • Make sure DISTINCTID -> computing using -> Table (down)

C. Worksheet Result

D. Important Notes

  • Measure Values and Measure Names need to work together to be effective.
  • The configuration for Aggregate Measures and Stack Marks can be different for every worksheet.

Tableau Special: Extract and Union Join Tables in PDF

A. Software

  • tableau prep builder 2019.2

B. Data Connection

  • PDF

C. Sample PDF document

page 9 of Odesope 2010

D. Data Interpreter

  • select “Use Data Interpreter”
  • drag “Page 9 Table 1” to “Add Data” canvas to start the flow
  • Effect size is recognized as character variable
  • In Input section, under Data Sample tab, select “Use all data”

E. Flow Development

  • Click “+” next to “Page 9 Table 1” in the flow and select “Add Union”
  • Union 1 shows there are 28 Rows of data with 7 Fields from “Page 9 Table 1”.
  • drag “Page 10 Table 1” over the Union 1 icon in the flow which means that “Page 10 Table 1” will be added to Union 1.
  • Union 1 now shows there are 58 Rows of data with 7 Fields from “Page 9 Table 1” and “Page 10 Table 1”.
  • add “Page 11 Table 1” to Union 1.

F. Add Calculation Field

  • Create a Calculated Field named [Effect Size] from the existing [Effect Size (g)]
  • Use split function to trim the ‘*’ from the string and then use float function to convert string to float.
  • Click ‘+’ next to Union 1 icon and select “Add Output”
  • Click Output icon to run flow
  • The output.hyper file will be generate under the c:\user\username\documents\My Tableau Prep Repository\Datasources\ folder

F. Use Case

  • Systematic Review/Meta-analysis

Tableau: Ratios

Below is a summary table by census topic and sub-topic vs. by geographic area. I want to show instead of the sum, the percentage.
tab007
Select Analysis from the menu. In the drop down menu, select Create Calculated Field…. Create “Percent of Total” with the following formula.
tab008
Drop the “Percent of Total” from Measures to the Text icon in Marks.
tab009

Tableau: Hierarchical Filter

  1. Organize data into hierarchy by drag dimension to other dimension. In the example, the Topic is a hierarchy structure, with L1Topic>L2Topic>Item.
    tab001
  2. Hierarchy structure can be used for Conditional filter. In the following examples, when the L1Topic is selected for Mobility or Labour main topic respectively, the L2Topic automatically shows only the associated sub-topics.
    tab003tab002
  3. Sort field by value of another field. Before sorting the L2Topic and Item fields, the rows are in alphabetical order as default.
    tab005
    Select sort for the field and choose sort by field, select Item ID as field name and choose sum as aggregation method.
    tab004
    The L2Topic and Item are now ordered by Item ID which doesn’t have to show in the result table.
    tab006

4. Hierarchical Filter Configuration

  • In the filter, select only relevant values for the hierarchical filter to take effect.
  • When data source are linked, the “Only Relevant Values”, “All Values in Hierarchy”, and “All Values in Database” may disappear, then you will not be able to use the hierarchical filter.