Practical examples of calculations and parameters

Let's turn our attention to some practical examples of row-level and aggregate calculations. The goal is to learn and understand some of what is possible with calculations. You will be able to build on these examples as you embark on your analysis and visualization journey.

Fixing data issues

Often, data is not entirely clean. That is, it has problems that need to be corrected before meaningful analysis can be accomplished. For example, dates may be incorrectly formatted, or fields may contain a mix of numeric values and character codes that need to be separated into multiple fields. Calculated fields can often be used to fix these kinds of issues.

We'll continue working with the Vacation Rentals data. You'll recall that the start and end dates looked something like this:

Without the year, Tableau does not recognize the Start or End fields as dates. Instead, Tableau recognizes them as strings. You might try using the drop-down menu on the fields in the data pane to change the data type to date, but without the year, Tableau will almost certainly parse them incorrectly, or at least incompletely. This is a case where we'll need to use a calculation to fix the issue.

Assuming, in this case, that you are confident the year should always be 2020, you might create calculated fields named Start Date and End Date.

Here is the code for getting the start date:

DATE([Start] + ", 2020")

And here is the code for getting the end date:

DATE([End] + ", 2020") 

What these calculated fields do is concatenate the month and day with the year and then use the DATE() function to convert the string into a date value. Indeed, Tableau recognizes the resulting fields as dates (with all the features of a date field, such as built-in hierarchies). A quick check in Tableau reveals the expected results:

Figure 4.15: The corrected dates appear next to the string versions. All fields are discrete dimensions on Rows (the dates are exact dates)

Not only are we able to fix problems in the data, but we can also extend the data and our analysis using calculations. We'll consider this next.

Extending the data

Often, there will be dimensions or measures you'd like to have in your data, but which are not present in the source. Many times, you will be able to extend your dataset using calculated fields. We already considered an example of creating a field for the full name of the guest where we only had first and last name fields.

Another piece of data that might unlock some truly interesting analysis would be the length of each rental. We have the start and end dates, but not the length of time between those two dates. Fortunately, this is easy to calculate.

Create a calculated field named Nights Rented with the following code:

DATEDIFF('day', [Start Date], [End Date])

The DATEDIFF() function takes a date part description, a start and an end date, and returns a numeric value for the difference between the two dates. We now have a new measure, which wasn't available previously. We can use this new measure in our visualizations, such as the Gantt chart of rentals, as follows:

Figure 4.17: The calculated field allows us to create the Gantt chart

You'll find many ways to extend your data with calculations as you continue your journey in Tableau. And that will enable you to accomplish some amazing analysis and visualizations. We'll consider some examples next.

Enhancing user experience, analysis, and visualizations

Calculations and parameters can greatly enhance the user experience, the analysis, and the visualizations.

Let's say we want to give the vacation condo manager the ability to do some what-if analysis. Every year, she offers a free night during the month of December. She wants to be able to see which renters would have received the free night, depending on which night she chooses.

To accomplish this, follow these steps:

1. If you have not done so, create a Gantt chart similar to what was shown earlier (following the field placement of the screenshot).
2. Create a parameter called Free Night with a data type of Date and a starting value of 12/12/2020. This will allow the manager to set and adjust the starting date for the promotional month. Show the parameter control by selecting Show Parameter Control from the drop-down menu on the Free Night parameter in the data pane.
3. Now, add a reference line to the view to show the free night. Do this by switching to the Analytics tab in the left sidebar. Drag Reference Line to the view and drop it on Table:
   

   Figure 4.18: Add a reference line by switching to the Analytics pane and dragging the reference line onto the canvas

4. In the resulting dialog box, set Line Value to Free Night. You may wish to set the Label to None, or Custom with the text Free Night. You may also wish to adjust the formatting of the line:
   

   Figure 4.19: Use the Edit Reference Line dialog to adjust formatting, labels, and tooltips

5. Create a calculated field called Gets Free Night that returns a true or false value, depending on whether the free night falls within the rental period:

   [Free Night] >= [Start Date]
   AND
   [Free Night] <= [End Date]
   

1. Place this new calculated field on the Color shelf.

We now have a view that allows the apartment manager to change the date and see a dynamically changing view that makes it obvious which renters would have fallen within a given promotional period. Experiment by changing the value of the Free Night parameter to see how the view updates:

Figure 4.20: The reference line will move, and the affected individuals will be recalculated every time you change the Free Night parameter value

The preceding view shows the proposed free night as a dashed line and highlights which rental periods would receive a free night. The line and colors will change as the apartment manager adjusts the Free Night parameter value.

In addition to extending your analysis, visualization, and user experience, you might also use calculations to add required business logic. We'll consider that next.

Meeting business requirements

Sometimes, data doesn't exactly match what your organization wants. For example, the measures in the data may not be the exact metrics required to make key business decisions. Or dimension values may need to be grouped according to a certain set of rules. Although this kind of business logic is often applied as data is transformed or modeled prior to connecting to it with Tableau, you may find cases where you need to implement business logic on the fly.

In this example, consider that the measure Rent is simply the base rent and does not include the discount or taxes. Those are separate fields in the data. If you needed to analyze the total Revenue, you'd need to calculate it. That calculation might look something like this:

[Rent] - [Discount] + ([Tax per Night] * [Nights Rented])

This formula takes the base Rent, subtracts the Discount, and then adds the Tax per Night multiplied by the Nights Rented. The parentheses aid readability but are not required because the multiplication operator, *, has higher precedence and is evaluated before the addition, +.

Up until now, we've created calculated fields that extend the data source. Sometimes you just need a quick calculation to help in a single view. We'll conclude by looking at these quick ad hoc calculations.

Ad hoc calculations

Ad hoc calculations allow you to add calculated fields to shelves in a single view without adding fields to the data pane.

Let's say you have a simple view that shows the Revenue per Guest, like this:

Figure 4.21: The revenue generated from each individual's stay

What if you wanted to quickly highlight any renters who had paid less than $1,500? One option would be to create an ad hoc calculation. To do so, simply double-click on an empty area of the Columns, Rows, or Measure Values cards, or on the empty space of the Marks shelf, and then start typing the code for a calculation. In this example, we've double-clicked the empty space on the Marks shelf:

Figure 4.22: Creating an ad hoc calculation on the Marks card

Here, we've entered code that will return True if the sum of Rent is less than $1,500 and False otherwise. Pressing Enter or clicking outside the text box will reveal a new ad hoc field that can be dragged and dropped anywhere within the view. Here, we've moved it to the Color shelf:

Figure 4.23: Using the ad hoc calculation on Color

The ad hoc field is only available within the view and does not show up in the data pane. You can double-click the field to edit the code.

Having seen a number of practical examples, let's conclude by considering some ways to ensure good performance when using calculations.