Understanding Simple Calculations in Tableau

In the previous chapters, you have learned much about visualizing data in various forms in Tableau. It is common to have cases where the data you want to visualize is not directly present in your dataset but can easily be obtained by applying transformations on the dataset or even aggregating the data in the dataset. For example, you may have the full address for a placed order, and you want to obtain only the country or city where the order came from. Alternatively, you may have data for each individual sale and you want to know the average of all sales. This and the subsequent two chapters will focus on calculations and how you can use these to answer such questions. This chapter will introduce calculations and cover simple calculations, while the next two will look at more advanced calculations.

The following topics will be covered...

Technical requirements

This chapter uses the following dataset:

• The Global Superstore dataset, which can be found at http://www.tableau.com/sites/default/files/training/global_superstore.zip.

Calculation basics

Calculations allow us to obtain more data points from the existing dataset and allow us to enhance our visualizations and analysis. Creating calculations in Tableau enables you to analyze data on the fly, without having to create overheads by transforming your original datasets every time you have a new idea about how you can analyze your data even further.

When to use calculations

Before we dive into calculations and how to create them, it is equally important to understand when to create calculations. Some examples of when calculations are useful are as follows:

• Changing datatypes: For example, you have data that is being represented as a string value.
• Performing mathematical calculations: For example...

Creating calculations

To create a calculation, click on Analysis | Create Calculated Field. Alternatively, you can scroll to the end of dimensions/measures tabs and right-click in the white space there.

You should see the following calculations window:

The top of the window, where it says Calculation1, is where you enter the name of your calculated field. The blank area underneath is where you enter your calculation. To the right side, you have a list of all of the available functions you can use and their syntax to quickly help you out. The tiny triangle icon to the right of the calculation editor can be used to toggle the functions window in/out.

Let's create a very simple calculation to see what our total profit would be if the average profit increased four times, and name this calculation 4x Profit, as shown in the following screenshot:

You will see, at the bottom of...

Building arithmetic calculations

To work with arithmetic calculations in Tableau, we can either use operators as discussed in the last section, work with one of the many number functions that are present, or make use of some of the aggregation options. This section will cover some of the important functions but it is not an exhaustive list. The Further reading section contains links to an A-Z list of functions maintained by Tableau.

Aggregation options

Aggregation options define how your data values are combined or aggregated. Simple aggregation works at the level of visual granularity.

What this means is the data is aggregated at the level of the dimensions in the visual. Without even studying it, we have used this logic...

Building string calculations

Similar to numeric calculations, many string functions exist to allow for various calculations. Let's look at some of the key string calculations and how they can be used.

Functions related to obtaining substrings from a string

One of the most common use cases when dealing with strings is that we want only parts of the string and not all. Many functions help us to do that. Let's have a look at what functions we can use.

LEFT: This follows the syntax LEFT (string, num_chars).

Using LEFT, the num_chars specified starting from the start of a string is returned. This becomes very useful for grouping.

For example, we want to find the customers who have ordered the most, based on what letter...

Building date calculations

Date calculations allow us to perform operations on dates, such as adding or subtracting dates and obtaining parts of dates. Let's look at some of the key functions in the following sections.

Obtaining the current date/time

Today() returns the current date in Tableau and NOW() can be used to return the current date-time. A common use case of this is to find the time taken between an event occurrence and today.

Obtaining parts of a date

• DAY(), MONTH() and YEAR(): They share the syntax of function (date). They can be used for obtaining the...

Building logical statements

Logical functions allow you to check conditions and see whether they are true or false. We will take a look at the various categories of logical functions over the coming sections.

Case statements

Case statements make use of the following syntax: CASE <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> ... ELSE <default return> END.

In case statements, the value of the expression is matched to each of the values and whenever a match is found, the return value corresponding to it is returned. If no value matches the expression, then the default else value is returned. If there is no default value, null values will be returned.

Case statements...

Other functions

Beyond the functions discussed so far, there are some other important functions that are useful and available in Tableau. One of those is the type conversion functions. Type conversion functions are useful for converting one data type into another, for example, from an integer into a string or from a float into an integer.

All of the following functions follow the common syntax of Function(expression):

DATE	It is used to obtain a date from numbers or strings or date expressions, for example, DATE("25/05/2019") returns #25-05-2019#.
DATETIME	Very similar to DATE, this function returns the date and time, for example, DATETIME("25 May 2019 13:40:00") returns #25-05-2019 13:40:00#.
FLOAT	This is used to cast to floating-point numbers, for example, FLOAT(2)=2.000.
INT	This is used to cast its argument to integers, for example...

Building grand totals and subtotals

Beyond showing the values from calculations, sometimes, we also want to show the values of the totals or subtotals in the same window as these values.

For instance, we have the following graph for SUM(Sales) split by Category and Sub-Category across segments:

Now, if we want to add the subtotals and the grand totals for both rows and columns, we can do the following:

1. Go to Analysis > Totals.
2. Select Show Row Totals | Show Column Totals. This will show the graph as follows:

3. Now if we want to see the subtotals too, we can select Add All Subtotals from Analysis | Tools. This will show the following:

If there is more than one dimension for which the subtotals are being calculated, then we can right-click on the dimensions to select or unselect, depending on whether we want the totals to be calculated or not.

For example, adding city to...

Summary

In this chapter, we have learned how to create simple calculations. We looked at the format and syntax requirements for the various elements of calculations. Following that, we looked in detail at some of the important functions and examples of number, string, date, and logical functions. Lastly, we also learned how to add totals and subtotals to our calculations. In the next chapter, we will learn about more advanced table calculations, where we will use the results of the calculations learned in this chapter to build on further calculations.

Further reading

To look at a list of all available functions in Tableau, go to the following link: https://help.tableau.com/current/pro/desktop/en-us/functions_all_categories.htm.