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:
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:
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:
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: