Like many of you, I am not a data scientist. My background is in engineering and marketing. I started Tableau with a lot of experience with Excel mixed with a bit of Access and I struggled for a while – that may sound familiar.
Additionally, spreadsheet calculators and Tableau are fundamentally different. Spreadsheets work on a cell basis – values or formulas are in cells – cells can be copied or referenced in formulas to return another single cell value. Tableau works with dimensions that categorize data and measures that are actual values. Referring to dimension returns all the associated values in the database – an entire column of values in a spreadsheet –
Ok, so what – it meant learning Tableau from the ground up and along the way 5 fundamental building blocks once learned could be combined to create even the most complex solutions. Below I will present the skills needed and a couple of examples. Try them out with your own simple data sets. You can't break anything and you will learn a lot in the process.
I grew up with a calendar on the refrigerator door – 7 columns for days and 4 to 5 rows for weeks and got comfortable seeing data organized in short wide tables. Spreadsheets have a similar structure so learning that Tableau needs a tall narrow data table structure is out of our comfort zone.
For example, spreadsheet column headers like dates need to be in a single dimension (column) with each value a different date
The way that is done is to pivot the data on the Data Source tab
resulting in the tall narrow data structure needed
Next, you need to understand how to combine data sets – using Joins to add a column or Unions to extend the length of the data
When you need to add another "column" of data to the table use a Join. In Excel, you would use a vlookup
To add Quantity data to the Sales data above with data that looks like this:
Alternatively, data that is updated regularly, like monthly Sales, using a Union of the new month to the existing data will append the new data to the bottom of the existing
There is one more way to combine data that are at different levels of aggregation or come from different data sources – that is Blending –
Load your first data source into Tableau then add a second source
Those are basic examples of 3 ways to connect data – it can be more complex but with practice, you will become comfortable selecting which to use – in later examples you will see how they affect your calculations
The order of operation is the sequence Tableau follows as it creates a viz.
For each worksheet, Tableau will use a subset of the total data set to create a table that "underlies" the viz. You can think of it as a spreadsheet – it's not really – it is the tall narrow format. Once the dimensional table is built – values are added and manipulated to create the final chart, graph, or visual
While there are 10 steps in the order of operation, I find it useful to think of them in 3 groups
The first 2 steps are workbook level and filter the data from the data source – they can be used to improve the performance of the book.
When I first started, I printed (yes on paper) a copy of the order of operation as a reference. Next to syntax mistakes, using steps out of order is the most frequent cause of errors.
While some of the formulas are similar to those in Excel, Tableau syntax is unique and needs to be learned. Fortunately, there is a Calculation Wizard to help
If you have written a lot of Excel formulas the wizard user interface (U/I) will look familiar. Formulas are listed and grouped into categories. A drop-down can filter the formula list making specific functions easier to find. On the right is an explanation of the formula you are working on and the arguments needed
Autofill and auto-suggest are also included – at the bottom of the frame is the Syntax is checked as you enter the formula.
The syntax checker will tell you when the formula is valid – that does not mean the logic returns the answer you expected.
The most common types of errors are due to aggregation / non-aggregation, trying to aggregate something that is already aggregated, mismatched parentheses, or missing an operator -I still make them every day – after a while, you know how to correct them – see aggregation to learn more about aggregation errors
You didn't learn Excel formulas overnight and the same is true for Tableau – you will find there are a few you use repeatedly that will be like second nature and others that you need to lookup.
Calculations are the backbone of creating more advanced analyses and the visuals that go along with them.
There are 4 basic types of calculations in Tableau
Simple calculations, including "If … Then " conditional statements look similar to those in excel except they are typically written at an aggregated level – Think back to the data structure, Dimensions and Measures refer to an entire "column" of Excel data –
so to find the average selling price at the level of dimensions in the viz –
ASP=Sum(sale)/Sum(quantity)
and this
If [Region]="East" or [Region]= "West" then "Costal"
Elseif [Region] = "Central" then "Middle"
else "South" end
and return this
Conditional statements can be nested or used in any other type of calculation
The more you write them the more comfortable you will become with them
Dates functions – real dates – are a special type of data in the Tableau – they self sequence, sort and most of all can be used in date calculations. The functions can be discrete or continuous and are based on truncating the full data time series at different levels
Some of the date functions – year, month, quarter, day seem familiar – others like datetrunc, dateadd, datediff will take some getting used to – see date functions for more
Like Excel, date functions come in 2 different types
- Those that return dates – Datetrunc in Tableau or Date Value in Excel
- and those that return a reference to the date like Month in both systems
Date functions can be used with time also. They are very flexible but do take some practice. I have several posts on converting strings to dates, calculating durations, and excluding weekends to use "Business Days" – see Business days
LOD Expressions – Extremely powerful, LOD's allow you to create additional layers in your data that are level above that entered when you loaded the data
{Fixed Segment, Region: Sum(Sales)}
In words says take all the combinations of Segment (4) and Region (4) and total the Sales anew save them for use later – and you have established a lay in the data set that includes 16 values.
Table Calculations With a background in Excel, table calculations will seem familiar.
Like Excel formulas, they operate on the rows and columns of the data table that underlies the worksheet but that is where the similarity ends.
In Tableau, there are 2 concepts – Scope and Direction that govern how the calculation is applied to the data in the table
Table calculations come in 3 types
- Those that aggregate or rank values in the table like
- Window_Avg, Running_Sum, Rank_Unique
- Those that aggregate or rank values in the table like
- Others used to navigate the table like:
- Lookup, Previous_Value, First, Last
- Others used to navigate the table like:
- Act as the interface to R or Python
- Script_Real, Script_Int
- Act as the interface to R or Python
You can use the Quick Table Calculator
Or you can write your own using the Calculation Wizard
The Direction for the calculation will default to Across but can be changed directly using "Compute Using" or opening the Editor
Filters, sets, and parameters give you and the user ways to select, limit, and group data.
Just like in Excel, filters limit the data in the underlying table, Data that is filtered out is no longer available for calculations or presentation on the worksheet.
Filters can be applied to the workbook level, to dimensions, or to measure values after they are added to the table
Measure filters can be applied at the record before aggregation or the after selecting an aggregation level
Sets classify dimensions into 2 groups – the "IN" group selected by the user and all other values are in the "OUT" group.
All records are still available in the table allowing comparisons between the In and the Out groups. Sets can be selected manually or by formula and multiple sets can be combined to identify items in both or only a single group. Set actions can be used to change set members by selecting from a value picker or dropdown.
Finally, sets are formed in Step 4 of the Order of Operation after Context filters have been applied but before Dimensional filters – giving the user several options when comparing values.
Parameters allow the user to input a single static value by a selection from a list or direct entry into the workbook. The value is constant until manual changed and is the same throughout the entire workbook.
But parameters don't do anything until they are included in a calculation (any type) or a filter. It is the formula or the filter that does affect the viz, not the parameter. The parameter value is the same everywhere in the workbook but it can be used in different calculations or filters and need not be applied to every worksheet. Changing the parameter value will affect all the calculations or filters where the parameter is used. Parameter actions can be used to change the parameter value visual elements on the worksheet
The parameter list can be updated dynamically when the workbook is opened –
Those are the 5 building blocks on the road to understanding Tableau. Combined simple calculations were used to create the viz below.
It uses a parameter action to select the date, set action to select states and segments, LOD's for YTD, and YoY calculations
It may seem formidable but with a little work on each, you will develop a deep understanding of how Tableau works.
The workbooks that accompany these examples can be found at Tableau Public Link
(Note: Originally presented on #Vizconnect – see the recording at https://www.youtube.com/watch?v=gZoVzJT3pZ4)
Also presented at TFF 2020 see Video Here
Jim
