Logic functions enable you to tell Tableau Public what to do when certain conditions are met. They are also known as conditional statements. The format is commonly referred to as IF THEN ELSE. You, as a programmer, ask Tableau to test whether a row of data meets a certain condition. If it does meet the condition, then there is an output, which can be another field, a discrete number, or a string. If the condition is not met, then you want a different output.
There are several logic functions in Tableau. Some of these functions are sub-functions or parts of others. We will focus on the following major functions:
- IF, followed by THEN, ELSE-IF, or ELSE: This tests whether a condition is met and show the result if it is met as well as other conditions that need to be tested and results that need to be produced in case none of the conditions are met.
- The IIF statement: This tests for a minimum of one condition and then provides the results when the condition is or is not met. It follows the same format as that of IF in Microsoft Excel.
- The IFNULL function: This identifies what Tableau Public should do in case a value is null. This is particularly useful for aggregations of dirty data sets, which are very common.
- The CASE statement: This tells Tableau Public what to do when a parameter or a string field has a very specific value. Unlike the CASE statements in ANSI SQL, Tableau Public does not accept aggregations in these.
- AND, OR, and NOT: These link or negate conditions that need to be met.
- END: This is critically important, as it terminates the loops of the IF and CASE statements. Tableau Public will tell you in case you need to add it and have not done so.
The IF statements are very useful. You can use them to group members and set thresholds, among many other uses. The condition that needs to be met can be a variety of things. In the first example, we will create a new measure in a transformation of the World Bank indicator data that we have extracted and transformed, and it's called population and land data.
We will create a new measure that gives us the total land area of a country. We want to use this measure to determine the percentage of each country's area that was flooded, but this measure does not exist in the Floods data, but it does exist in the Population and Land Data. Later in this chapter, we will blend the data sources to create a calculated field that uses it in the denominator.
The Population and Land Data does not have a metric called total area. The way the data source is structured, the measures are in rows and not columns, with a field representing the corresponding metric value, as shown in the following screenshot. We need to create a calculated field that states that if Indicator Name is Land area (sq. km), then we want the Indicator Value to appear as follows:
In order to do this, we will follow these steps:
- Create a new calculated field in the Population and Land Data source by clicking on the Context menu in the Data window and selecting Create Calculated Field.
- Name the calculated field Area of Country.
- Start an IF statement that says that if the Indicator Name is Land area (sq. km), then Tableau Public should use Indicator Value. Otherwise, it should use 0.
- End the statement.
- Validate that there are no error messages.
- Click on OK. The result is shown in the following screenshot:
In this statement, if we did not include the ELSE statement, then the products of the field would be null for every value of Indicator Value that isn't specified, and we do not want nulls, because performing any kind of mathematical operation on a null value results in a null value even when you're adding or multiplying it by valid numbers. So, we included else 0, even though we did not necessarily need to do so, in order to preserve the integrity of the data set.
Another good use of an IF statement is to create groups or establish performance thresholds. In the World Bank Indicators data source, there is a field that identifies the percentage of its GDP that each country spends on public health. The first quartile is at five percent, the median is at seven percent, and the upper quartile is at nine percent. In the following example, we will use mathematical functions to group the countries according to what their average expenditure on public health is. (You can download this data set from www.worldbank.org).
In this example, we have hard-coded the values in the inequalities, which means that the thresholds used for the groups are not dynamic. In the next chapter, we will use window calculations to make them dynamic.
We created a simple box plot that shows the maximum health expenditure as a percentage of the GDP per country in 2010. Since we put Country on the Detail shelf, Tableau Public graphs one mark per country. In the calculated field, we even accounted for null values, as represented in the ELSE statement. Then, we put this new calculated field on the Color shelf of the box plot, as shown in the following screenshot:
Perform the following steps to create this graph:
- Add Health: Health expenditure, total (% GDP) to the Columns shelf.
- From the Analysis menu, deselect Aggregate Measures.
- Drag Date to the Filters shelf and select YEAR.
- Filter YEAR to 2010.
- Drag Military: Expenditures (% GDP) to the Size shelf.
- Drag Country to the Detail shelf on the Marks card.
- Create a new calculated field called Health Expenditure Quartile, and provide Tableau Public with several conditions to test.
- Drag the new field named Health Expenditure Quartile to the Color shelf.
- Adjust the colors so that the lower quartiles are in red and the upper quartiles are in blue.
This IF statement is similar to the two other types of logic statements in Tableau Public, namely IIF and CASE.
The structure of IIF is the same as that of the IF statements in excel, and it's very similar to the IF statement that we just created in that you are asking Tableau Public to test a condition and then provide the results according to the logical outcome, which can be either true or false. In the following example, we will ask Tableau Public whether the last letter of the name of each country is A.
There are two possible outcomes—yes, the condition is met, and no, it is not met. We tell Tableau Public what value to use for each possible outcome. We can use a string, a number, another field or aggregation, or even nothing at all.
In the following example, which is for demonstration purposes, we ask Tableau Public to test whether each country name in the global major floods data, which was trimmed in the section on string functions, ends with the letter A. If the condition is met, the result should be blank (which is different from NULL), but in case it is not met, then the outcome should be the last letter of the country:
