Orange Belt Classes:

1. Drilling Down

Drilling down is a function of Tableau Hierarchies. There are three types of hierarchies: Tableau built-in, DataSelf built-in, and custom workbook hierarchies. Built-in hierarchies cannot be modified in the Web App. All date type fields are subject to the Tableau built-in hierarchy of Year-Quarter-Month-Day. In the example below, the ShipTo Country is a DataSelf built-in hierarchy.

Drill Down with Built-in Hierarchies

  1. Create a report showing Sales by ShipTo Country by Year, with Inv Date Year column filtered for 2022.

  2. Using the '+' icon on the Columns and Rows or column headers, drill down to the next hierarchy levels:

    1. Country → State → City (DataSelf built-in hierarchy).

    2. Year → Quarter → Month → Day (Tableau built-in hierarchy).

    3. Watch the video (no audio) for all variations.

Drill Down with Custom Hierarchies

  • Create a custom hierarchy to drill down from Customer → Inv No → Inv Date → Item.

  • On the left panel, right-click Customer → Hierarchy → Create Hierarchy → name it Customer.Invoice.Item → Ok.

  • Add the Invoice No, Inv Date, and Item fields to the hierarchy
    Method 1: Drag the fields right below the Customer dimension inside of the new hierarchy.
    Method 2: Right click on the field, select Hierarchy → Add to Hierarchy → Customer.Invoice.Item

  • Create a view showing Sales by Customer.

  • Drill down all the way to Item.

  • Add All Subtotals, and Column Totals to Top.

TIP 1: A dimension cannot be in more than one hierarchy. ShipTo State already belongs to a DataSelf built-in hierarchy created in the Tableau data source (it can only be changed using Tableau Desktop). To work around such limitations, you can right-click a dimension → Duplicate. This will create a copy of that dimension and rename it. You can use the duplicated field in custom hierarchies.

TIP 2: If a date field is inserted into a hierarchy, it no longer follows the default Tableau hierarchy of Year→ Qtr→ Month→ Day. You can duplicate the date field and use the duplicate in the hierarchy so the original date field still follows the default Tableau date hierarchy

Hands-on Exercise: Custom Hierarchies
Create a hierarchy to drill down from ShipTo State → Salesperson → Customer.

Drill Down with Dashboards

You can use a dashboard to drill down from Customer ↔︎ Item Cat ↔︎ Item.

  1. Build a report showing Sales by Customer with the Totals at the top.

  2. Duplicate the report (right-click its bottom tab → Duplicate). Replace Customer with Item Cat.

  3. Duplicate the report again, replace Item Cat with Item.

  4. Build a dashboard with these 3 views side by side. Set Size to Automatic.

  5. Set all dashboard objects to Use as Filter.

  6. Click data points to explore and understand the interactivity.

Hands-on Exercise: Drill Down with Dashboards

  1. Create a dashboard with Salesperson sales in 2022, and a second one showing sales details: Inv Dates (from Inv Date Options hierarchy), Inv No, Customer, Item, and measures: Sales and Qty Sold. Make the Salesperson report a Use as Filter. See if you can make it look like the following image.

    Click the following image to view how to build the above dashboard:

Drill Down into the Underlying Data

  1. Select a data point on a report.

  2. On the pop-up menu, click the View Data grid icon.

  3. A pop-up window displays with a summary of the data.

  4. Click the Full Data tab to see the underlying data for the selected records.

  5. Click on the Show Fields field in the upper right. Scroll to the top of the list to select All Fields.
    This displays all the detail data for the selected records.
    Click on the Download icon in the upper right to download as a CSV file.

  6. To download all the data in a chosen format, click on the download icon in upper right of the main screen. Select Data to download all data records as a CSV.

2. Tableau Date Filters

Wizard-Driven Date Filters

  1. Build a report showing Sales by Year → Quarter → Month.

  2. Drag and drop Inv Date into the Filters shelf. Note the different kinds of date filters available.
    The first two (relative & range) are continuous date types. The others are discrete date types. We will discuss this more later on.

    Select Range of Dates as a starting point. Then right click to show the filter.

  3. Click the arrow-down on the top-right o the filter card and explore the options:

    1. Range of Dates: enter any start and end date.

    2. Relative Date: Select from a variety of popular relative date periods compared to today’s date.

    3. Start Date

    4. End Date

    5. Browse Periods: today, 1 week back, 30 days back, 3 months back, 1 year back, 5 years back.

Watch the video below to view the options.

4. Example of using a discrete date filter instead of continuous date filter.

Delete the Invoice Date field from the Filter section then add it again.
This time select #Year instead of Range of Dates. Then show the filter.
Note the different options.

Year over Year (YoY) at the most basic level

The report below shows a YoY representation of Sales by Customers in two years for one month only (Jun). It’s sorted descending in 2020. Note that some of the top customers in 2020 are doing poorly in 2021.

Hands-on Exercise

Change the report above to compare YoY sales in the 2nd quarter.

Year to Date (YTD) at the most basic level

See below for a YTD report (Jan through Jun) manually selecting all the months to the present for YTD.

Hands-on Exercise

  • Change the report above to compare YTD through April for the past 3 years.

Month to Date (MTD) at the most basic level

See below for a report showing Month-to-Date sales in April through the 10th in 2020 and 2021.

3. DataSelf Date Option Filters

DataSelf adds special Date Option fields to enhance reporting and filtering. See the example below. Usually, these Date Option fields match with the company’s fiscal period. Depending on your source system, these fields might come preconfigured to the calendar year or to the company’s custom fiscal year. Consult DataSelf for details.

FYear, FQuarter, FMonth, and FY/M

For many clients, these fields overall work the same way as Tableau’s. Benefits of these fields:

  • Customizable drill down (ex.: FYear → FMonth, no need to go to FQuarter).

  • Customizable content (for instance, show FMonth as FP 01 instead of 01).

  • Customizable to work with non-calendar based fiscal periods (such as 4-5-4 periods).

  • Easier to use in formulas (ex.: the number of a fiscal month is FMonth No, which is simpler than using Tableau’s formula: month (Inv Date)).

  • Sometimes the FY/M format is very practical for filters or report headers.

Hands-on Exercise

  • Create a report showing sales by FMonth by Customer in FY 2022.

To-Date Periods: [YTD Fiscal], [QTD Fiscal], [MTD Fiscal]

The fields YTD Fiscal, QTD Fiscal and MTD Fiscal look at your fiscal period to-date up to today’s date.

The following 3 examples consider a fiscal year starting on Feb the 1st and ‘today’ as 03/26/2021. Therefore, YTD Fiscal = YTD includes Feb 1 to Mar 26, and YTD Fiscal = After YTD covers Mar 27 to Jan 31.

Hands-on Exercise

  • Create a report showing YTD Sales in FY 2021 and FY 2022 by Customer (sales from Feb 1 to Mar 26).

  • Optional: Create a report showing FY 2022 MTD Sales for FMonths 01 and 02.

Relative Periods: [FYr 0 = This FYr], [FMo 0 = This FMo]

The field FYr 0 = This FYr look at your fiscal year, where 0 represents this fiscal year, -1 for last fiscal year, -2 for two fiscal years ago, 1 for fiscal year. The same concept applies to FMo 0 = This FMo, where 0 represents this fiscal month.

The report below shows sales in the prior month (-1) compared to the same month in the prior year (-13).

Rolling 30-Days

The field Rolling 30-Days looks at a moving 30-day window starting today.

The report below shows the use-case for sales in the last 30-days (0 = Feb 24 to Mar 25) compared to the prior 30-day period (-1 = Jan 25 to Feb 23).

4. Formatting Measures

  • Right-click the green pill of a report measure (on Rows, Columns, Mark shelf or Measures shelf) → Format Number → set the formatting options below:

    • Automatic.

    • Number: set decimal places, units (such as K for thousands) and thousand separators.

    • Currency: set currency with decimal places, units (such as K for thousands) and thousand separators.

    • Percentage: set decimal places.

    • Scientific: set decimal places.

5. Report Title with Variables

For this exercise, build a report showing Sales by Customer filtered by Salesperson, and let’s add to the report title the Salesperson filter selection and the time when the data was updated:

  • Hover the mouse over the report title → click the arrow down on the top right → Edit Title.

  • On the title expression, a colon after <Sheet Name>, then click Insert → Salesperson → Apply.

    • See the change in the report title. Change the Salesperson filter values a see the title changes.

  • On the title expression, go to the next line, type in “As of “, click Insert → Data Update Time → Ok.

    • See the change in the report title.

Here are some of the options available in the Insert dropdown:

  • Data Update Time: Date/time when the Tableau data source was refreshed last time.

    • With certain data sources, you might want to use the data source’s Data as of field. This field is populated by the ETL and usually has a more accurate data extraction date/time. To use it: drag and drop it into the report’s Detail mark (center panel), and then insert it in the report title.

  • Sheet Name (the tab or view name).

  • Workbook Name (the Tableau workbook name).

  • The bottom section of the list shows data structures used in the report such as dimensions, measures, filters and parameters.

6. Colors and Charts 101

Tableau excels at showing data beautifully and artfully. The following are popular examples of using colors and charts in reports and dashboards.

Grid Report: Gradient Colors

To contrast or highlight large and small values, drag the desired measure to the Colors shelf. See the following example:

  • Drag Sales from the Data panel onto the Color shelf.

  • Scroll the values to see the colors based on Sales amount.

  • Hover over Sales and click the Sort icon to see the top to bottom color fading.

Grid Report: Stepped Colors

Use stepped colors to highlight segmented values, such as above and below a certain amount. For example:

  • Click the arrow down on the color legend → Edit Colors → select a Palette (ex.: Red-Black Diverging)set Stepped color to 2 → Show Advanced → check Center → type in 1000000 → Enter → close the pop up.

  • Note that sales above 1M are now in black, the others in red.

Grid Report: Highlight Tables

Use Show Me → Highlight Tables icon to use background colors on grid reports (see video). Feel free to explore the color techniques shown above, such as changing to stepped colors.

  • Use a report with a single measure → Show Me → Highlight Tables (top right icon) → Show Me.

Bars

  • Drag a dimension (Items) to Rows.

  • Drag a measure (Sales) to Columns.

  • Sort: click Sort icon.

  • Pivot: click Swap Rows and Columns icon.

  • Labels: Drag the measure from the data panel onto the Label mark (center panel).

  • Formatting Labels: right-click the measure green pill on the center panel by the T icon → Format Number → format it as desired.

Bars Sliced by Other Dimensions

  • Duplicate the view above.

  • Drag Company from the data panel onto the Color mark (center panel).

    • That’s it! Bars Sliced by another Dimension.

  • Duplicate the view above.

  • Drag Company from the data panel onto Columns.

    • That’s it! Bars Segmented by another Dimension.

  • Drag Company from Columns to Rows.

Trend Lines by Year, Month and Year-over-Year

  • Drag Inv Date to Columns.

  • Drag measure to Rows.

    • That’s it! Trend line by year!

  • Duplicate the view above.

  • Right-click the YEAR(Inv Date) blue pill in Columns → Month (May 2015).

    • That’s it! Trend line by month!

  • Duplicate the view above.

  • Right-click the MONTH(Inv Date) green pill in Columns → Month (May).

  • Drag Inv Date from the data panel and drop onto the Color mark (center panel).

    • That’s it! Trend line by Year-over-Year!

  • Labels and formatting: try the same as done in the Bars section.

Sales by State

  • Select ShipTo State, press and hold the Ctrl button, select Sales.

  • On the top right, click Show Me and select the Maps icon on the center of the pop up.

  • Click Show Me again to hide the pop up.

    • That’s it! Sales by State!

  • From the view above, click Show Me on the top right, and select the Maps icon on the left.

  • Click Show Me again to hide it.

  • Drag Company from the data panel onto the Color mark (center panel).

  • On the Marks panel (center panel), click the dropdown list → Pie.

  • On the Marks panel (center panel), click the Size icon and drag it to the right.

    • That’s it! Sales by State by Company!

  • Labels and formatting: try the same as done in the Bars section.

Show Me Button

The Show Me button (top right) creates a view based on the fields already used in the view and any fields you’ve selected in the Data pane. Ex:

  • Build a report showing Sales by Salesperson.

  • Click the Show Me button on the top right, and explore the options available. See video.

  • Why many options are grayed out? Build a report showing Sales by Salesperson by Year and explore the options available.

    • Show Me enables options that apply to the fields selected in your report.

    • The orange square highlighted option is Tableau’s best guess of the best visualization for the selected fields.

  • Click here further Tableau info.

Dual Axis

  • Let’s see Sales and GP% trends on a dual axis chart.

  • Build a “trend line by month” chart like done above.

  • Drag GP % into Rows.

    • This shows Sales and GP% on a single X axis.

  • Click Show Me button → Dual Combination icon.

  • Click Show Me again to hide it.

    • Now this chart has dual Y axis headers, left with Sales, right with GP%.

  • To limit the chart’s period and seeing trends more clearly:

    • Filter report to start on Jan 2017 forward.

    • Click the Size in the Marks center panel, and drag it to the left.

    • To manually apply the Dual Axis feature, right click on the 2nd measure in the rows and select the option for Dual Axis

Next: Orange Belt: Report Design Part 3