7. Data Analysis: Creating and Using Calculations

Overview

In this chapter, you will learn to create and use various types of calculations, not just within an existing data source, but also across data sources. This chapter first describes the definitions and the differences between Aggregate and Non-Aggregate values. Then, you will learn about various types of calculations, such as numeric calculations, string calculations, and date calculations, as well as how to write logic statements in Tableau.

By the end of this chapter, you will be able to create and use various types of calculations in Tableau.

Introduction

Typically, the first step when analyzing data is to start with some questions or goals. It could be as simple as determining your most profitable customers, or more complicated, such as investigating which products are leading to losses despite high sales. After deciding on questions or goals, you would audit your data. This means identifying where data resides—whether the required fields are stored in a single or in multiple data sources and whether all fields are readily available for use. Then, you would check the integrity and validity of your data. This means checking whether the data needs any modifications in terms of cleaning, combining, or restructuring.

Once data is audited, the tools in Tableau Desktop allow you to explore it visually for more streamlined analysis. This can mean building charts, adding interactivity, separating data into groups, or creating calculations to derive more meaningful insights. Once analysis is complete, the insights you...

Creating and Using Ad hoc / Edit in Shelf Calculations

Ad hoc / Edit in Shelf calculations are the quickest and easiest way to create a new calculated field in Tableau. Ad hoc calculations can be created in the Rows, Columns, and Measure Values shelves, as well as in the Marks cards.

Simply double-click on the existing field in your shelf of choice, or, alternatively you can use the Edit in Shelf option in the drop-down list of that field, as shown in the following figure:

Figure 7.10: A screenshot showing how to create an ad hoc calculation

These ad hoc calculations are useful when creating quick, on-the-fly calculations that you may or may not want to save and reuse. You will explore this in the exercise below.

Exercise 7.01: Creating an Ad Hoc Calculation to Highlight Loss-Making Sub-Categories

The aim of this exercise is to find out which sub-categories have negative profit and which ones have positive profit. Those with negative profit will...

Creating and Using Different Types of Calculations

Tableau is a simple yet versatile tool, and the ability to create calculations gives users the flexibility to perform powerful analysis, which can help with decision-making. Most of the time, creating calculations in Tableau is a fun experience, but sometimes it can be a little frustrating as well, especially if you are coming from a different platform to Tableau and are trying to replicate some functionality. The way these tools are structured and designed is different and trying to replicate the functionality from one tool in another can make the experience frustrating. The best way to avoid frustration while creating calculations in Tableau is to start small and get acquainted with the functions that Tableau has to offer. While writing a calculation in Tableau is easy, it is recommended that, if possible, you should try to use the built-in native features first, instead of creating a new calculated field. Some examples of these...

Handling Null Values while Creating and Using Calculations

Often, you might deal with data containing null values. These could be genuine entries in the data. For example, there may not be any Sales value to report against a particular product—even though it is part of the inventory, it may not have been sold yet. These nulls could also be because of some data entry errors. Most likely, you would identify and take care of these nulls at the data preparation stage. However, that may not always be the case. At times, you may need to tackle them within Tableau Desktop using calculations. Null values tend to pose a problem when used in calculated fields, simply because when doing arithmetic operations on fields, it may result in the output being null in Tableau. Refer to the following screenshot:

Figure 7.51 – A screenshot showing the Excel data and the output of the calculation on fields with null values

The preceding screenshot is a quick mockup...

Creating Calculations across Data Sources

In earlier sections of this chapter, you have seen how to create and use calculations, but all these calculations were done within the same data source. Having all your data in one source would be an idealistic scenario; however, that may not always be the case, and you may have to deal with data coming from multiple sources. This means you may have to compute calculations across data sources, too.

In this section, you will focus on how to create calculations across data sources using data blending. You will also look at how to create and use calculated fields to join data. You have already seen the data blending and join functionality in previous chapters, and you will use that knowledge to create and use calculations across data sources.

You will use the Modified CoffeeChain data along with Budget Sales for CofeeChain.xlsx. These can be downloaded at the following links:

• https://packt.link/EK3uu.
• https://packt.link...

Summary

In this chapter, you explored some important aspects involved in creating and using calculations in Tableau and studied the difference between aggregate and non-aggregate fields. You looked at numeric, string, and date calculations, and learned to write logic statements and handle null values. Finally, you looked at how to use these calculations across data sources.

In upcoming chapters, you will move on to more advanced table and level of detail calculations, which will allow you to do even more with your data.