Number Functions

This article introduces number functions and their uses in Tableau. It also demonstrates how to create a number calculation using an example.

Why use number functions

Number functions allow you to perform computations on the data values in your fields. Number functions can only be used with fields that contain numerical values. For more information, see Data Types.

For example, you might have a field that contains values for the variance in your budget, titled Budget Variance. One of those values might be -7. You can use the ABS function to return the absolute value of that number, and all the other numbers in that field.

The calculation might look something like this:

ABS[Budget Variance]

And for that -7 value, the output would be 7.

Number functions available in Tableau

ABS

SyntaxABS(number)
OutputNumber (positive)
DefinitionReturns the absolute value of the given <number>.
Example
ABS(-7) = 7
ABS([Budget Variance])

The second example returns the absolute value for all the numbers contained in the Budget Variance field.

NotesSee also SIGN.

ACOS

SyntaxACOS(number)
OutputNumber (angle in radians)
DefinitionReturns the arccosine (angle) of the given <number>.
Example
ACOS(-1) = 3.14159265358979
NotesThe inverse function, COS, takes the angle in radians as the argument and returns the cosine.

ASIN

SyntaxASIN(number)
OutputNumber (angle in radians)
DefinitionReturns the arcsine (angle) of a given <number>.
Example
ASIN(1) = 1.5707963267949
NotesThe inverse function, SIN, takes the angle in radians as the argument and returns the sine.

ATAN

SyntaxATAN(number)
OutputNumber (angle in radians)
DefinitionReturns the arctangent (angle) of a given <number>.
Example
ATAN(180) = 1.5652408283942
Notes

The inverse function, TAN, takes the angle in radians as the argument and returns the tangent.

See also ATAN2 and COT.

ATAN2

SyntaxATAN2(y number, x number)
OutputNumber (angle in radians)
DefinitionReturns the arctangent (angle) between two numbers (x and y). The result is in radians.
Example
ATAN2(2, 1) = 1.10714871779409
NotesSee also ATAN, TAN and COT.

CEILING

SyntaxCEILING(number)
OutputInteger
DefinitionRounds a <number> to the nearest integer of equal or greater value.
Example
CEILING(2.1) = 3
NotesSee also FLOOR and ROUND.
Database limitations

CEILING is available through the following connectors: Microsoft Excel, Text File, Statistical File, Published Data Source, Amazon EMR Hadoop Hive, Amazon Redshift, Cloudera Hadoop, DataStax Enterprise, Google Analytics, Google BigQuery, Hortonworks Hadoop Hive, MapR Hadoop Hive, Microsoft SQL Server, Salesforce, Spark SQL.

COS

SyntaxCOS(number)

The number argument is the angle in radians.

OutputNumber
DefinitionReturns the cosine of an angle.
Example
COS(PI( ) /4) = 0.707106781186548
Notes

The inverse function, ACOS, takes the cosine as the argument and returns the angle in radians.

See also PI. To convert an angle from degrees to radians, use RADIANS.

COT

SyntaxCOT(number)

The number argument is the angle in radians.

OutputNumber
DefinitionReturns the cotangent of an angle.
Example
COT(PI( ) /4) = 1
NotesSee also ATAN, TAN and PI. To convert an angle from degrees to radians, use RADIANS.

DEGREES

SyntaxDEGREES(number)

The number argument is the angle in radians.

OutputNumber (degrees)
DefinitionConverts an angle in radians to degrees.
Example
DEGREES(PI( )/4) = 45.0
Notes

The inverse function, RADIANS, takes an angle in degrees and returns the angle in radians.

See also PI().

DIV

SyntaxDIV(integer1, integer2)
OutputInteger
DefinitionReturns the integer part of a division operation, in which <integer1> is divided by <integer2>.
Example
DIV(11,2) = 5

EXP

SyntaxEXP(number)
OutputNumber
DefinitionReturns e raised to the power of the given <number>.
Example
EXP(2) = 7.389
EXP(-[Growth Rate]*[Time])
NotesSee also LN.

FLOOR

SyntaxFLOOR(number)
OutputInteger
DefinitionRounds a number to the nearest <number> of equal or lesser value.
Example
FLOOR(7.9) = 7
NotesSee also CEILING and ROUND.
Database limitations

FLOOR is available through the following connectors: Microsoft Excel, Text File, Statistical File, Published Data Source, Amazon EMR Hadoop Hive, Cloudera Hadoop, DataStax Enterprise, Google Analytics, Google BigQuery, Hortonworks Hadoop Hive, MapR Hadoop Hive, Microsoft SQL Server, Salesforce, Spark SQL.

HEXBINX

SyntaxHEXBINX(number, number)
OutputNumber
DefinitionMaps an x, y coordinate to the x-coordinate of the nearest hexagonal bin. The bins have side length 1, so the inputs may need to be scaled appropriately.
Example
HEXBINX([Longitude]*2.5, [Latitude]*2.5)
NotesHEXBINX and HEXBINY are binning and plotting functions for hexagonal bins. Hexagonal bins are an efficient and elegant option for visualising data in an x/y plane such as a map. Because the bins are hexagonal, each bin closely approximates a circle and minimises variation in the distance from the data point to the centre of the bin. This makes the clustering both more accurate and informative.

HEXBINY

SyntaxHEXBINY(number, number)
OutputNumber
DefinitionMaps an x, y coordinate to the y-coordinate of the nearest hexagonal bin. The bins have side length 1, so the inputs may need to be scaled appropriately.
Example
HEXBINY([Longitude]*2.5, [Latitude]*2.5)
NotesSee also HEXBINX.

LN

SyntaxLN(number)
Output

Number

The output is Null if the argument is less than or equal to zero.

DefinitionReturns the natural logarithm of a <number>.
Example
LN(50) = 3.912023005
NotesSee also EXP and LOG.

LOG

SyntaxLOG(number, [base])

If the optional base argument isn't present, base 10 is used.

OutputNumber
DefinitionReturns the logarithm of a number for the given base.
Example
LOG(16,4) = 2
NotesSee also POWER LN.

MAX

SyntaxMAX(expression) or MAX(expr1, expr2)
OutputSame data type as the argument, or NULL if any part of the argument is null.
Definition

Returns the maximum of the two arguments, which must be of the same data type.

MAX can also be applied to a single field as an aggregation.

Example
MAX(4,7) = 7
MAX(#3/25/1986#, #2/20/2021#) = #2/20/2021#
MAX([Name]) = "Zander"
Notes

For strings

MAX is usually the value that comes last in alphabetical order.

For database data sources, the MAX string value is highest in the sort sequence defined by the database for that column.

For dates

For dates, the MAX is the most recent date. If MAX is an aggregation, the result will not have a date hierarchy. If MAX is a comparison, the result will retain the date hierarchy.

As an aggregation

MAX(expression) is an aggregate function and returns a single aggregated result. This displays as AGG(expression) in the viz.

As a comparison

MAX(expr1, expr2) compares the two values and returns a row-level value.

See also MIN.

MIN

SyntaxMIN(expression) or MIN(expr1, expr2)
OutputSame data type as the argument, or NULL if any part of the argument is null.
Definition

Returns the maximum of the two arguments, which must be of the same data type.

MIN can also be applied to a single field as an aggregation.

Example
MIN(4,7) = 4
MIN(#3/25/1986#, #2/20/2021#) = #3/25/1986#
MIN([Name]) = "Abebi"
Notes

For strings

MIN is usually the value that comes first in alphabetical order.

For database data sources, the MIN string value is lowest in the sort sequence defined by the database for that column.

For dates

For dates, the MIN is the earliest date. If MIN is an aggregation, the result will not have a date hierarchy. If MIN is a comparison, the result will retain the date hierarchy.

As an aggregation

MIN(expression) is an aggregate function and returns a single aggregated result. This displays as AGG(expression) in the viz.

As a comparison

MIN(expr1, expr2) compares the two values and returns a row-level value.

See also MAX.

PI

SyntaxPI()
OutputNumber
DefinitionReturns the numeric constant pi: 3.14159...
Example
PI() = 3.14159
NotesUseful for trig functions that take their input in radians. See also RADIANS.

POWER

SyntaxPOWER(number, power)
OutputNumber
DefinitionRaises the <number> to the specified <power>.
Example
POWER(5,3) = 125
POWER([Temperature], 2)
NotesYou can also use the ^ symbol, such as 5^3 = POWER(5,3) = 125

See also EXP, LOG and SQUARE.

RADIANS

SyntaxRADIANS(number)
OutputNumber (angle in radians)
DefinitionConverts the given <number> from degrees to radians.
Example
RADIANS(180) = 3.14159
NotesThe inverse function, DEGREES, takes an angle in radians and returns the angle in degrees.

ROUND

SyntaxROUND(number, [decimals])
OutputNumber
Definition

Rounds <number> to a specified number of digits.

The optional decimals argument specifies how many decimal points of precision to include in the final result. If decimals is omitted, number is rounded to the nearest integer.

Example
ROUND(1/3, 2) = 0.33
Notes

Some databases, such as SQL Server, allow specification of a negative length, where -1 rounds number to 10s, -2 rounds to 100s and so on. This is not true of all databases. For example, it is not true of Excel or Access.

Tip: Because ROUND may run into issues due to the underlying floating point representation of numbers – such as 9.405 rounding to 9.40 – it may be preferable to format the number to the desired number of decimal points rather than rounding. Formatting 9.405 to two decimal places will yield the expected 9.41.

See also CEILING and FLOOR.

SIGN

SyntaxSIGN(number)
Output-1, 0 or 1
DefinitionReturns the sign of a <number>: The possible return values are -1 if the number is negative, 0 if the number is zero or 1 if the number is positive.
Example
SIGN(AVG(Profit)) = -1
NotesSee also ABS.

SIN

SyntaxSIN(number)

The number argument is the angle in radians.

OutputNumber
DefinitionReturns the sine of an angle.
Example
SIN(0) = 1.0
SIN(PI( )/4) = 0.707106781186548
Notes

The inverse function, ASIN, takes the sine as the argument and returns the angle in radians.

See also PI. To convert an angle from degrees to radians, use RADIANS.

SQRT

SyntaxSQRT(number)
OutputNumber
DefinitionReturns the square root of a <number>.
Example
SQRT(25) = 5
NotesSee also SQUARE.

SQUARE

SyntaxSQUARE(number)
OutputNumber
DefinitionReturns the square of a <number>.
Example
SQUARE(5) = 25
NotesSee also SQRT and POWER.

TAN

SyntaxTAN(number)

The number argument is the angle in radians.

OutputNumber
DefinitionReturns the tangent of an angle.
Example
TAN(PI ( )/4) = 1.0
NotesSee also ATAN, ATAN2, COT and PI. To convert an angle from degrees to radians, use RADIANS.

ZN

SyntaxZN(expression)
OutputAny, or o
Definition

Returns the <expression> if it is not null, otherwise returns zero.

Use this function to replace null values with zeros.

Example
ZN(Grade) = 0
NotesThis is a very useful function when using fields that may contain nulls in a calculation. Wrapping the field with ZN can prevent errors caused by calculating with nulls.

Create a number calculation

Follow along with the steps below to learn how to create a number calculation.

  1. In Tableau Desktop, connect to the Sample - Superstore saved data source, which comes with Tableau.

  2. Navigate to a worksheet and select Analysis > Create Calculated Field.

  3. In the calculation editor that opens, do the following:

    • Name the calculated field Minimum Sales transaction

    • Enter the following formula:

      MIN(Sales)

    • When finished, click OK.

The new number calculation appears under Measures in the Data pane. Just like your other fields, you can use it in one or more visualisations.

When Minimum Sales is placed on Text on the Marks card in the worksheet, its name is changed to AGG(Minimum Sales), which indicates that it cannot be aggregated any further, since it is already aggregated down to the lowest level of detail (the smallest sales value for all records).

This example shows the minimum sales per category.

When subcategory is brought into the view, the minimum sales for each subcategory are shown.

See Also

Functions in Tableau

Tableau Functions (Alphabetical)

Tableau Functions (by Category)

Formatting Calculations in Tableau

Thanks for your feedback!Your feedback has been successfully submitted. Thank you!