Number Calculations in Tableau

When faced with creating a calculated field you may notice a long list of abbreviations with a short description on what they are and how they are used. However, some may be confusing so I will be helping you understand the most useful calculations under the Numbers tab.

ABS - Returns the absolute value of a given number

Example: ABS(-7) = 7

Why is this useful? - If you need to do a division but have one value that is negative, the return will be negative. Using ABS fixes this.

CEILING/FLOOR - rounds a number to the nearest integer of equal or GREATER/LESSER value.

CEILING example: CEILING(3.1415) = 4

FLOOR example: FLOOR(3.1415) = 3

Why is this useful? - Can be used if you want to overexaggerate or underplay your values in certain use cases. You would not want to inflate your profit numbers but may want to overexaggerate your complaints from customers.

DIV - Returns the integer part of a division

Example: DIV(11,2) = 5

HEXBINX/Y - Maps an (x,y) co-ordinate to the X or Y (depending on the calc you choose) of the nearest hexagonal bin.

Example:

Why is this useful? - When faced with many data points that overlap each other, hexagonal binning is your best friend. This essentially uses density to display the number of data at a given point/area. This is done by mapping hexagons to group the data in different areas for better data aggregation around the bin centre.

MAX/MIN - Returns the MAXIMUM/MINIMUM of a single expression across all records.

Example: MAX (4,7) = 7

Example: MIN (4,7) = 4

Why is this useful? Returning the maximum or minimum value for a category.

POWER - Returns result of a number raised to a given power

ROUND - Rounds number to the nearest integer or to a specific decimal place

Example - ROUND(3.1415, 1) = 3.1

Why is this useful? For displaying your findings/data in a better, more appropriate way. For example, it is unlikely you will be showing a percentage to 3 decimal point. Having the ability to choose what you're rounding to is also a nice feature.

SIGN - Returns the sign of a number

If the number is positive then 1 is returned. If the number is zero then zero will be returned. If the number is negative then -1 is returned.

Why is this useful? - Highlighting all of the positive and negative values by colour on a bar chart. This video explains a real life use case for this. LINK

SQRT - Returns square root of a number

SQUARE - Returns square of a given number

ZN - Returns <expression> if it is not null, otherwise return 0

This calculation changes all null values to zero.

Why is this useful? When making calculations it is important, even if there is no data for it, to have some data present rather than a null.

EXAMPLE

To conclude, get to know everything that can be done with your calculated fields! A lot are really useful.

Author:
George Ledger
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2024 The Information Lab