The ability to add calculations to your viz is key to using Tableau to present insights and tell the story the way you want to.   

See the video : link to Basic Calcs

In this section, we will look at the language of Tableau, the syntax, and how to write some basic calculations.  In the 3 sections that follow we’ll get into 3 special topics – .

Date Functions, 

LOD’s and 

Table Calculation

What about this language thing – is it hard?  

Not really but like any other software Tableau uses some keywords, functions, and phrase structures that make up the language of Tableau.  If you are coming from Excel many of the functions have similar or the same names but take care they do not necessarily work the same way. 

Now the best news, there is a calculation wizard to help you write and debug your custom formulas.  

Start by opening the “hamburger” on the Data Frame and select Create Calculated Field and the wizard opens.  You can select the Category and specific field from the drop-down on the right or just start typing in a calculation – Tableau will auto-fill as you type and suggest functions.  Once selected, an explanation, with examples appears on the right – Pretty cool!! –

One last thing, as you type a syntax checker at the bottom of the window follows your progress and lets you know when the formula is “valid”  – if not it will explain the error when you hover over the text – Remember – it is only checking the SYNTAX, not the logic – you are on your own for that.

You have probably noted that when measures are added to the table they are aggregated (with sum() as a default)  and then all the other dimensions and measures in the viz have to be at the same level.  Why?

That is one of the powerful aspects of Tableau,  

The data you enter into Tableau is at a very low level – (often called the grain)  – In the world of retail that level is equivalent to the row level of a sales invoice.  In the Superstore data I use here there are 10,000 records,  

So if we want to see only 3 values – the total sales by Category those 10,000 values need to be aggregated up through the product and customer hierarchy till there are only 3 – you don’t need to worry about that – Tableau does that as you create the structure for the data table in the Order of Operations. 

Tableau maps each of the records to the 3 Category Dimension values and sums the values of all sales at that level.  

Okay that is clear but is often confusing to the new user and happens when writing more complex formulas generates an aggregation error.   Sales have been aggregated to 3 records but Quantity is not aggregated – it is still 10,000 records – the error is simply saying that sales and quantity need to be at the same level – the solution – aggregate with  sum(Quantity)

Now you might be saying – “Aren’t there non-aggregated calculations’?  Yes there are – they are known as Row-level calculations – Let’s see the difference

First at the ASP Row Level = Sales/Quantity (I am using the data set from the Connections Section 1) aggregate level then  ASP Agg Level = Sum(Sales)/Sum(Quantity) and at the at the lowest level in the data (row level), the individual calculations return the same values

At levels above the detail (row level)  the Aggregate properly combines the data whereas the Row Level simply adds the individual row results.  There are times you will need to use Row Level calculations but you will use Aggregate level calculations much more frequently.

Writing calculations are similar to what you have done in the past – the functions may have a different name and need to be used in different combinations which is best learned through practice.  Here are a few of the more frequent calculations:

• • • • ASP:  Sum(Sales)/Sum(Quantity)
      • Calculated average per order:     Sum(Sales)/Countd(Order ID)
      • Calculated average per customer:  Sum(Sales)/Countd(Customer Name)
      • Profit ratio:  Sum(Profit)/Sum(Sales)

Take a moment to look at the calculations –  Measures – Sales, Quantity, and Profit are aggregated with Sum() although you can choose from various functions

Dimensions, on the other hand, can only be aggregated with one of 5 functions:

• • Count  –  Counts all records
  • Countd – Counts unique records
  • Min – Alphanumeric minimum
  • Max – Alphanumeric maximum
  • ATTR – used when several different dimension values will be aggregated in the same expression

The calculation is then added to the data table and each record (cell) is loaded with the value returned by the formula.  The calculated field can then be used in any other calculations or used directly in the viz like any other measure.  Tableau will aggregate the calculation to the proper level as required by the Dimensions and filters you have applied in the viz and total the field properly

Frequently you will need a calculation that is dependent on the value of a dimension or measure.  In spreadsheet calculations you use an IF statement – In Tableau you use an IF … THEN statement

The syntax is     

IF (test) then (result)

Elseif(test2) then (result2)

Elseif(testn) then (resultn)

End

Some examples will make it easier to understand:

The condition checks the Category name (value) and if it exactly matches “Furniture” it returns the Sales value – all other Categories are set to 0

A slightly more advanced calculation checks the name of the Region and increments the Sales to different levels – Notice the use of Else  in the last clause – it is equivalent to “all other values” 

Now as you progress, you will find opportunities to use Boolean (expressions that return a True or False ) as a filter – here the formula checks to see if the total value of Sales is greater than 5000.  Not the If / Then  are omitted and the calculation returns a True or a False which I used to filter the view

Now it’s your turn – the way to learn calculations is to write some –

In the next 3 sections, we will look at special topics – Date Functions – LODs – Table Calculations