We might know that the naming convention of the rental unit in the vacation rental data actually gives us the room number and the name of the building. For example, the unit named 207-Beach Breeze is room 207 of the Beach Breeze condo complex.

In the Chapter 04 workbook, create a couple of calculated fields.

Name the first Room with the following code:

SPLIT([Rental Property], "-", 1) 
 

Then, create another calculated field named Building with the following code:

SPLIT([Rental Property], "-", 2) 

Both of these functions use the Split() function, which splits a string into multiple values and keeps one of those values. This function takes three arguments: the string, the delimiter (a character or set of characters that separate values), and the token number (which value to keep from the split, that is, 1^st, 2^nd, 3^rd, and so on.) Using the - (dash) as the delimiter, the Room is the first value and Building is the second.

Using the two calculated fields, create a bar chart of Revenue per Building and Room, similar to this:

The Building and Room fields show up in the data pane under Dimensions. The calculated dimensions can be used just like any other dimension. They can slice the data, define the level of detail, and group measures.

Row-level calculations are calculated at a row level, but you can choose to aggregate the results. For example, you could aggregate to find the highest Room number (MAX) or count the distinct number of Buildings (COUNTD). In fact, if the result of a row-level calculation is numeric, Tableau will often place the resulting field under Measures by default. As we've seen before, the default use of a field can be changed from a measure to a dimension, or vice versa, by dragging and dropping it within the data pane.

Note that Tableau adds a small equals sign to the icon of the fields in the data pane to indicate that they are calculated fields:

The code for both calculated fields is executed for every row of data and returns a row-level value. We can verify that the code is operating on a row level by examining the source data. Simply click on the View Data icon next to dimensions to see the row-level detail (it's next to the magnifying glass icon in the preceding screenshot). Here, the new fields of Building and Unit, along with the row-level values, can be clearly seen:

You can also build calculations that use other calculations. This is referred to as nesting, and the resulting calculations are called nested calculations.

For example, let's say that you know that the floor of a room is indicated by its number. Rooms 100 through 199 are on the first floor, and 200 through 299 are on the second. You'd like to have that information available for analysis.

We could potentially add this attribute to the source data, but there are times when this may not be an option or may not be feasible. We may not have permission to change the source data or the source might be a spreadsheet that is automatically generated every day, and any changes would be overwritten.

Instead, we can create a row-level calculation in Tableau to extend the data. To do so, create a calculated field named Floor with the following code:

IF LEFT([Room], 1) = "1"
THEN "First Floor" 
ELSEIF LEFT([Room], 1) = "2" 
THEN "Second Floor" 
END 

This code uses the LEFT() function to return the leftmost character of the room. Thus, 112 gives a result of 1; 207 gives a result of 2. The IF THEN END logic allows us to assign a result (either First Floor or Second Floor), depending on which case is true. Notice that you used the Room field in the calculation, which, in turn, was another calculation. There is no limit to the levels of nesting you can use.

To account for additional cases, we might simplify our calculation to the following:

LEFT([Room], 1) 

This code simply returns the leftmost character of the room number. We'll get 3 for 306 and 8 for 822. But what if we have room numbers such as 1056 on the 10^th floor, and 1617 on the 16th? We'd have to consider other options, such as the following:

MID([Room], 0, LEN([Room]) - 2) 

Although this is more complicated, the string functions return a substring that starts at the beginning of the string, but stop short of the last two characters. That gives us floor 10 for 1025, and floor 18 for 1856.

To wrap up our row-level examples, let's create one more calculation. Create a new calculated field called Full Name with the following code:

[First name] + " " + [Last name] 

This code concatenates the strings of First name and Last Name with a space in-between them. We now have a single field that will display the full name of the individual renter.