Number functions in Tableau are important tools for handling numeric data. These functions let you perform calculations, create custom fields, and turn raw numbers into actionable information. Whether you’re rounding values, calculating totals, or performing aggregations, these functions are crucial for working with numbers in Tableau. Industries such as finance, sales, and marketing rely heavily on these functions for tasks such as profit analysis, sales trends, or performance comparisons. Number functions offer flexibility, making them a must-have tool for extracting meaningful information from data.
Types of Number Functions in Tableau
ABS()
To get the absolute value (positive) of any number, useful in analysis where negative values should be considered positive.
Syntax: ABS(number)
Example: ABS(-100) returns 100.
CEILING()
Rounds a number to the nearest integer, helpful in inventory counts.
Syntax: CEILING(number)
Example: CEILING(2.5) results in 3.
FLOOR()
Rounds a number to the nearest integer, which is ideal for pricing models.
Syntax: FLOOR(number)
Example: FLOOR(2.9) results in 2.
ROUND()
Rounds numbers to specific decimal places, which is useful in formatting financial data.
Syntax: ROUND(number, num_digits)
Example: ROUND(3.14159, 2) results in 3.14.
SIGN()
Returns -1 for negative, 1 for positive, 0 for zero, which is useful in profit/loss comparisons.
Syntax: SIGN(number)
Example: SIGN(-150) returns the result of -1.
SQRT()
Returns the square root of a number, used in risk assessments or financial models.
Syntax: SQRT(number)
Example: SQRT(16) returns the result of 4.
POWER()
Raises a number to a specific exponent, useful in calculating compound growth.
Syntax: POWER(number, exponent)
Example: POWER(2, 3) returns the result of 8.
PI()
Provides the value of π for calculations related to circles.
Syntax: PI()
Example: PI() returns 3.14159.
EXP()
Raises e to the power, useful in population growth or projections.
Syntax: EXP(number)
Example: EXP(1) results in 2.71828.
LN()
Returns the natural logarithm (base e) of a number, often used in financial data.
Syntax: LN(number)
Example: LN(2.71828) returns 1.
LOG()
Returns the logarithm to the specified base, useful in measuring growth.
Syntax: LOG(number, base)
Example: LOG(100, 10) returns 2.
ZN()
Converts null values to zero, useful for preventing broken calculations in reports.
Syntax: ZN(expression)
Example: ZN([sales]) returns 0 if sales are zero.
Importance of Number Functions in Tableau
Data Manipulation
You can easily clean, adjust, and manipulate numerical data using functions like ROUND() or ABS(), which simplifies the analytical process.
Custom Calculations
These functions help create custom metrics for deeper insights, such as using POWER() for growth projections or EXP() for financial forecasting.
Simplifies Complex Calculations
Tableau’s built-in functions mean you don’t need external tools to perform calculations, keeping your data accurate and well-organized.
Ensuring Consistency
Functions like CEILING() or FLOOR() ensure consistency in your reports, which is a key aspect in finance or healthcare where accuracy matters.
Steps to perform several Number Functions in Tableau
Step 1: Open Tableau
Step 2: Click on Text file to connect with Tableau
Step 3: Browse csv file (or other file format as your need) and click on Open
Step 4: Data has loaded. Now take a look at the data for further action/processing.
ABS Function
Step 5: Right click over Product Price and select Create Calculated Field… to create a field with absolute price
Step 6: Specify a name (AbsolutePrice) for field and fill the following:
Output for ABS Function:
Note:
- It returned positive value for Product Price.
CEILING Function
Step 8: Right click over Sales Amount and select Create Calculated Field… to create a field with Ceiling Amount of Sales
Step 9: Specify a name (CeilingAmount) for field and fill the following:
Output for CEILING Function:
Note:
- It returned ceiling value for Sales Amount.
SIGN Function
Step 11: Right click over Product Price and select Create Calculated Field… to create a field with Sign of Price
Step 12: Specify a name (PriceStatus) for field and fill the following:
Output for SIGN Function:
Note:
- It returned sign of Product Price (in 1, -1, 0)
SQRT Function
Step 14: Right click over Stock Remaining and select Create Calculated Field… to create a field for storing square root value of Stock Remaining
Step 15: Specify a name (SQRT_StockRemaining) for field and fill the following:
Output for SQRT Function:
Note:
- It returned square root value of Stock Remaining value.
POWER Function
Step 17: Right click over Quantity and select Create Calculated Field… to create a field for storing power value of quantity
Step 18: Specify a name (Power_Quantity) for field and fill the following:
Output for POWER Function:
Note:
- It returned Power value of Quantity value.
Conclusion
Number functions in Tableau provide powerful ways to work with quantitative data, from rounding numbers to calculating complex metrics like growth. They help analysts manipulate data efficiently, maintain consistency in reports, and simplify complex calculations. For businesses, they unlock deeper insights and make data-driven decisions. Across industries, these functions make Tableau a robust tool for analyzing and interpreting numerical data.
If you like the article and would like to support me, make sure to:
🔔 Follow Me: LinkedIn| Youtube | Instagram | Twitter
👏 Like for this article and subscribe to our newsletter
📰 View more content on my DataSpoof website
