Orange Belt: Report Design Part 2
1. Downloading the Data
Select a data point on a report.
On the pop-up menu, click the View Data grid icon.
A pop-up window displays with a summary of the data.
Click the Full Data tab to see the underlying data for the selected records.
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.
To download all the data in the data set to 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, Excel or PDF.
2. Tableau Date Filters
Wizard-Driven Date Filters
Build a report showing Sales by Year → Quarter → Month.
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.
Click the arrow-down on the top-right o the filter card and explore the options:
Range of Dates: enter any start and end date.
Relative Date: Select from a variety of popular relative date periods compared to today’s date.
Start Date
End Date
Browse Periods: today, 1 week back, 30 days back, 3 months back, 1 year back, 5 years back.
See the meme 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.
Basic date comparison views using discrete date filters
Basic Year over Year (YoY) view
This view shows a basic YoY representation of Sales by Customers in two years for one month only (June). The Year(InvDate) and Month(InvDate) were added as discrete date filters. This shows sales in June for 2020, 2021.
Basic Year to Date (YTD) view
See below for a basic YTD report (Jan through Jun) manually selecting all the months to the present for YTD.
Basic Month to Date (MTD) view
See below for a basic Month-to-Date view of sales in April 1 - 10 in 2020 and 2021.
An additional DAY(InvDate) filter was added to manually select days in the month.
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. Even though these fields are stored in a hierarchy, they are not designed for drill down purposes. DataSelf used the hierarchy solely to group these custom fields together.
FYear, FQuarter, FMonth, and FY/M
For calendar based Fiscal Calendars, these date fields function similarly to the standard Inv Date field. 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 view showing sales by FMonth by Customer for 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
Related Pages:
Search terms: relative date, relative period, dynamic dates