Join Curt Frye for an in-depth discussion in this video Presenting Tableau operators and built-in functions, part of Tableau 9 Essential Training.
- One of the best aspects of the Tableau program is the selection of built-in functions and operators that let you create calculations quickly. In this movie, I will describe some of the more frequently used operators and built-in functions, and I hope it will save you time and help you gather important information about your business. The first operator is the +, that's used for addition. And one thing I should note, is that it's also for addition using dates. So for example, if you have a date, August 2nd, 2015, and you want to find out what would be 10 days beyond that, you would get August 12th, 2015, just by adding the number 10.
You could also use the + if you have text or string data to concatenate two values together. The - is used for subtraction and negation, and again, that works with dates as well. The * is used for multiplication. The / for division. And if you need to do modular division, which returns the remainder, then you use the %. So for example, five % two, or five mod two, would return one, which is the remainder.
You often see this used if you are packing cases of bottles, or perhaps cans of a product, and you want to know how many you'll have left over. The caret is used for exponents, so for example, two caret three, is two to the third power, or eight. And then you also have the usual series of comparison operators. == is used for identical to. The = tests for equality between values, it can also be used for assignment.
Then you have the >, <, >=, or <= symbols, or operators. Not equal to, which is either the ! followed by an =, or a < followed by a >. And you also have three logical operators, AND, OR, and NOT. Now let's talk about some commonly used functions. The most common, and most common by far actually, is SUM, which finds the sum of a set of values.
Perhaps a field, perhaps a column from a table, and so on. Average finds the average, that's AVG. MIN and MAX find the smallest and largest values of a data set. So if you have the values one through 10, one would be the MIN, and 10 would be the MAX. STDEV, or standard deviation, finds the standard deviation of a set of values. If you're doing business analysis, perhaps of a process, and you want to know what the standard deviation is, you'll use standard deviation to measure variants in a process, perhaps if you're filling bottles, or if you have order fulfillment times, you can use the standard deviation to analyze the amount of variants in a process.
Next is ABS, which returns the absolute value, or distance from zero, of a number. So the absolute value of six would be six, where the absolute value of -5 would be five. CEILING rounds a number up to the next integer, so if you have 23.1, that would be rounded up to 24. And FLOOR rounds the number down to the previous integer, so if you had 23.9, that would be rounded down to 23.
And finally, you have ROUND, which has two arguments, a number, and then the number of decimal points. What that does is round a number up or down to a specified number of digits, and that includes digits after the decimal point. The ROUND function rounds the number up if the decimal value is 0.5 or greater, and down if it is less than 0.5. So for example, 25.5 would be rounded up to 26, and 25.49 would be rounded down. If you round to a number of decimals, say for example, you round 25.49 to the first decimal place, that would be rounded to 25.5.
This is just a quick look at the number of functions that are available to you. There are quite a few, so I encourage you to dig into the Help system and find the ones that you need for your analysis.
- Connecting to a data source
- Joining related data sources
- Creating visualizations
- Displaying the data underlying a workbook
- Creating custom calculations and fields
- Sorting and filtering data
- Adding crosstabs
- Creating heat maps, charts, and basic maps
- Building a dashboard