If you've played around with Tableau for a short while you may have come across calculated fields. These are calculations which can be performed to any fields within Tableau to generate new fields which may be useful for a particular piece of analysis. You may have also noticed that certain functions apply to certain data types...
In this blog we will focus solely on the 'Number' calculated fields. These functions, rather intuitively, apply to numerical data. We will now discuss some of the more common ones (and some of the lesser used functions as well!)
Functions for rounding
There are three functions which can be used for rounding data, and they are the following:
ROUND(number, [decimals]) : this rounds a number to the specified number of decimal points. If this number is negative it will round to the nearest 10, 1oo etc. So for example ROUND(154.367, -1) = 150
CEILING(number): rounds the number to the nearest integer of equal or greater value. e.g. CEILING(37.6)=38
FLOOR(number): rounds the number to the nearest integer of equal or lesser value. e.g. FLOOR(37.6)=37
CEILING and FLOOR can certainly be useful in many situations. For example in capacity planning it is better to round up and account for a slight over capacity than to round down and underestimate.
Exponentials and Logs
The use case for these functions is very situational but we will discuss them nonetheless:
EXP(number): returns the exponential of the number
LN(number): returns the natural logarithm of the number
LOG(number, [base]): returns the logarithm of the number with a given base
It may be necessary to have a logarithmic scale for certain data types but this will be rare.
SIGN and ZN Functions
Two more very useful functions which do the following:
SIGN(number): returns the sign of the number entered: for positive values +1, for zero, 0, and for negative values -1.
ZN(expression): returns expression if it is not null, otherwise returns 0. This is useful for removing null values but be mindful this will impact any aggregations such as means in the dataset as zeros are now being added.
MIN and MAX Functions
These do exactly what they say on the tin... very useful for finding extreme values within records.
MIN(expression) or MIN(expr 1, expr 2): returns the min of all records or the min of two values
MAX(expression) or MAX(expr 1, expr 2): returns the max of all records or the min of two values
HEXBIN Functions
There are two functions in this class:
HEXBINX(number, number) and HEXBINY(number, number). For the given location of a point (denoted by the numbers), these functions will return the x and y coordinate of the nearest hexagonal bin. If for example you have high density spatial data with lots of overlapping points, they can be useful for ensuring data points are isolated.
Why hexagons? Well they tesselate (can fit together) without leaving gaps and do so very compactly.
Angles and Trigonometric Functions
These are some lesser used functions in day to day visualisations but can be useful in more artistic charts where the angle between elements is important.
DEGREES(number) and RADIANS(number): converts number from radians to degrees and degrees to radians respectively
PI(): returns the number pi (3.14...), useful for giving angles in radians
SIN(angle), COS(angle), TAN(angle), COT(angle): given an angle in radians, these return the sine, cosine, tangent or cotangent
ASIN(number), ACOS(number), ATAN(number), ATAN2(y number, x number): for a given number or numbers performs the respective inverse trigonometric functions to obtain an angle in radians.
Miscellaneous Functions:
A few odd functions which may be of use...
SQRT(number): returns the square root of the number
SQUARE(number): squares the given number
DIV(integer, integer): divides the first integer by the second and only gives the integer part of that result i.e. DIV(11,2)=5
And there you have it, an extensive list of calculated field functions for numbers.