Calculations

Use Calculations to write spreadsheet-style formulas.

info

Users will need Can Edit permissions.

Hex calculations allow you to write ad-hoc formulas using a familiar spreadsheet interface. Creating a formula will add a new column to your table.

Calculations can be created wherever you use Tables (in a Table Display cell, or in the Table tab of a SQL cell or Chart cell). To get started, click the + icon at the right of a table. The formula bar will open and a new column will appear on the right. Then, type a formula using the syntax in the language documentation below. To reference columns, begin typing to bring up a list of matching column names. Use the arrow keys to move up and down the suggestions list, and select the desired column by hitting Tab or Enter. Alternatively, click a column in the table to reference it in your formula.

A preview of your calculated values will be rendered as you type. Hit Enter or ⌘ + Enter to run the calculation in ernest. The calculation will be applied to every row in the table.

To edit an existing calculation, either click the fx icon in the column header or click the column’s header and select Edit formula….

Calculated columns can be filtered, formatted, and sorted, just like a regular column. If a cell with a calculation has a cell-level filter applied to it, the calculation will run before the filter is applied.

Formulas

The Hex calculation language is designed to be familiar to spreadsheet users. You can perform basic arithmetic using mathematical operators:

numerator / denominator

You can compose functions to perform more sophisticated logic:

If(end_date < Today(), 'Closed', 'Active')

The data types, operators, and functions that are available in the Hex calculation language are documented below.

Parameterized calculations

It's possible to make calculations dynamic based on project variables. You can use Jinja to reference Python variables in calculation formulas.

Variables can be referenced in a formula using syntax like the following: {{variable_name}}. See Using Jinja for more details on Jinja usage.

warning

Complex jinja logic such as if statements, for loops, and references to lists are not supported.

Changing an input parameter upstream will reactively update parameterized calculations downstream.

Data types

The calculation language presents four data types: Text, Number, Boolean, and Datetime. They map to the underlying dataframe data types as follows:

Calcs Type	Pandas Type	SQL Type	Example Literals
Text	object	VARCHAR, CHAR	'Hello!', '', null
Number	int64, float64	INT, BIGINT, SMALLINT, FLOAT, DOUBLE, DECIMAL	42, -42.0, 2.5e12, null
Boolean	bool	BOOLEAN	True, False, null
Datetime	datetime64	DATE, DATETIME, TIMESTAMP	'2020-01-28 04:08:10', null

Operators

Operator	Description	Input Data Types	Return Type
&	Concatenates two text values.	Text & Text	Text
+	Adds two numbers.	Number + Number	Number
-	Subtracts one number from another.	Number - Number	Number
*	Multiplies one number by another.	Number * Number	Number
/	Divides one number by another. Hex automatically protects you from divide-by-zero errors.	Number / Number	Number
^	Raises one number to the power of another.	Number ^ Number	Number
%	Modulo. Returns the remainder when dividing one number by another.	Number % Number	Number
AND / &&	Logical AND	Boolean AND Boolean	Boolean
OR / ||	Logical OR	Boolean OR Boolean	Boolean
NOT / !	Logical NOT	Boolean NOT Boolean	Boolean
=	Evaluates if one value equals another.	Number = Number Text = Text Datetime = Datetime	Boolean
>=	Evaluates if one number is greater than or equal to another.	Number >= Number Datetime >= Datetime	Boolean
>	Evaluates if one number is strictly greater than another.	Number > Number Datetime > Datetime	Boolean
<=	Evaluates if one number is less than or equal to another.	Number <= Number Datetime <= Datetime	Boolean
<	Evaluates if one number is strictly less than another.	Number < Number Datetime < Datetime	Boolean
!= / <>	Evaluates if one value is not equal to another.	Number != Number Text != Text Datetime != Datetime	Boolean

Functions

📝 Text

Function	Description	Example
Concat(text_1, ..., text_n)	Concatenates multiple text values together into one. Equivalent to the & operator. Inputs text: A text value to concatenate. Any number of additional text values can be added. Return type text Example Concat('Jane', ' ', 'Doe') → ‘Jane Doe’	Concat('Jane', ' ', 'Doe') → ‘Jane Doe’
Length(text)	Counts the number of characters in text. Inputs text: A text value to measure the length of. Return type number	Length('Hex') → 3
Left(text, n)	Returns the first n characters of text. Inputs text: A text value to take the first n characters of. n: The number of characters to take. Return type text	Left('abcd', 3) → ‘abc’
Right(text, n)	Returns the last n characters of text. Inputs text: A text value to take the last n characters of. n: The number of characters to take. Return type text	Right('abcd', 3) → ‘bcd’
Lower(text)	Converts a text value to lowercase. Inputs text: A text value to make lowercase. Return type text	Lower('Jane Doe') → ‘jane doe’
Upper(text)	Converts a text value to uppercase. Inputs text: A text value to make uppercase. Return type text	Upper('Jane Doe') → ‘JANE DOE’
Contains(text, search_text)	Returns True if text contains search_text, and False otherwise. Inputs text: A text value to search in.search_text: A text literal to search for withintext. Cannot be an expression or reference. Return type boolean	Contains('abcd', 'abc') → True
StartsWith(text, search_text)	Returns True if text starts with search_text, and False otherwise. Inputs text: A text value to search in. search_text: A text literal to search for at the start of text. Cannot be an expression or reference. Return type boolean	StartsWith('abcd', 'abc') → True
EndsWith(text, search_text)	Returns True if text ends with search_text, and False otherwise. Inputs text: A text value to search in. search_text: A text literal to search for at the end of text. Cannot be an expression or reference. Return type boolean	EndsWith('abcd', 'bcd') → True
Substitute(value, search_text, replacement_text)	Changes all occurences of search_text in value to be replacement_text. Inputs value: A value to search in. search_text: A sub-value to search for inside of value. replacement_text: The value to replace search_text with. Return type text	Substitute('abcabc', 'bc', 'BC') → 'aBCaBC'

🧮 Math

Function	Description	Example
Abs(number)	Computes the absolute value of number. Inputs number: A number to take the absolute value of. Return type number	Abs(-3) → 3
Ceiling(number)	Rounds up number to the nearest integer. Inputs number: A number to round up. Return type number	Ceiling(3.14) → 4
Floor(number)	Rounds down number to the nearest integer. Inputs number: A number to round down. Return type number	Floor(3.14) → 3
Power(number, power)	Raises number to the power of power. Functionally equivalent to ^. Inputs number: A number to raise by the power of power. power: The power to raise number by. Return type number	Power(3, 2) → 9
Sqrt(number)	Takes the square root of number. Inputs number: The number to take the square root of. Return type number	Sqrt(9) → 3
Exp(power)	Raises the mathematical constant e to the power of power. Inputs power: The power to raise e by. Return type number	Exp(1) → 2.718281828459045
Round(number)	Rounds number up or down to the nearest integer. Inputs number: The number to round. Return type number	Round(3.14) → 3

🔀 Logical

Function	Description	Example
If(condition, value_if_true, value_if_otherwise)	Returns value_if_true if condition is True, and value_if_otherwise otherwise. Inputs condition: An expression resulting in True or False. value_if_true: A value to return if condition is True. value_if_otherwise: A value to return if condition is not True. Must be the same data type as value_if_true. Return type text, number, boolean, datetime	If(num % 2 = 0, 'Even', 'Odd')
Switch(switch_value, if_matches_1, result_1, ..., if_matches_n, result_n)	A more succinct and readable way of nesting many If() statements based on one single input value. Returns the result value corresponding to the paired if_matches value that equals the switch_value. Inputs switch_value: The value to compare against all the if_matches values. if_matches_1: The first value to compare against switch_value. If they are equal, the function returns result_1. Otherwise, it compares switch_value to the next if_matches value. result_1: The value to return if switch_value equals if_matches_1. Return type text, number, boolean, datetime	Switch(status_code, 1, 'Processing', 2, 'Confirmed', 3, 'Shipped', 4, 'Delivered')
Coalesce(value_1, ..., value_n)	Returns the first non-null value. Inputs value_1: A value to return if not null. If null, the next value input is considered, and so on. Return type text, number, boolean, datetime	Coalesce(null, 0, 42) → 0
IsFinite(number)	Returns True if number is not null, not NaN, and not Inf. Otherwise, returns False. Inputs number: A number to check for being finite. Return type boolean	IsFinite(42) → True
IsOneOf(value, match_1, ..., match_n)	Returns True if value equals any of match. Inputs value: A value to compare against all the match values. match_1: The first value to compare against value. If they are equal, the function returns True. Otherwise, it compares value to the next match value. Return type boolean	IsOneOf(1, 2, 3, 4) → False (1 is not equal to 2, 3, or 4)
IsNull(value)	Returns True if value is null, and False otherwise. Inputs value: A text, number, boolean, or datetime value to check for being null. Return type boolean	IsNull(null) → True

🪄 Casting

Function	Description
ToText(number)	Converts number into a text data type. Inputs number: A number to convert to text. Return type text Example ToText(123) → ‘123’
ToNumber(text)	Converts text into a number data type. Inputs text: A text value to convert to a number. Return type number Example ToNumber('123') → 123
ToDatetime(text)	Converts text into a datetime data type. Inputs text: A text value to convert to a datetime. Return type datetime Example ToDatetime('2024-02-19') → 2024-02-19T00:00:00
ToBoolean(number)	Converts number into a boolean data type, where 0 is False and 1 is True. Inputs number: A number to convert to a boolean. Return type boolean Example ToBoolean(0) → False

📅 Date & Time

info

Note that calculations using literal datetime values (ex. '2024-01-22 12:15:50') must first be cast to datetime format using ToDatetime(). For example, Day('2024-02-01') becomes Day(ToDatetime('2024-02-01')).

Datetime calculations using column references need not be cast.

Function	Description
Year(datetime), Quarter(datetime), Month(datetime), Day(datetime), Hour(datetime), Minute(datetime), Second(datetime), Millisecond(datetime)	Extracts the date part specified in the function name from datetime. Inputs datetime: A datetime value to extract from. Return type number Example Day('2024-02-14') → 14
TruncYear(datetime), TruncQuarter(datetime), TruncMonth(datetime), TruncWeek(datetime), TruncDay(datetime), TruncHour(datetime), TruncMinute(datetime), TruncSecond(datetime)	Truncates datetime down to the nearest datepart as specified in the function name. Inputs datetime: A datetime value to be truncated. Return type datetime Example TruncMonth('2024-02-14 10:31:50') → 2024-02-01 00:00:00
DiffDays(start_datetime, end_datetime), DiffHours(start_datetime, end_datetime), DiffMinutes(start_datetime, end_datetime), DiffSeconds(start_datetime, end_datetime), DiffMilliseconds(start_datetime, end_datetime)	Calculates the amount of time between start_datetime and end_datetime. If start_datetime is after end_datetime, the result will be negative. Inputs start_datetime: A datetime value to calculate from. end_datetime: A datetime value to subtract from start_datetime. Return type number Example DiffSeconds('2024-02-03 04:05:06', '2024-02-03 04:05:08') → 2
DayOfWeek(datetime)	Extracts the day of the week from datetime (1 for Sunday, 7 for Saturday). Inputs datetime: A datetime value to extract from. Return type number Example DayOfWeek('2024-01-02') → 3
Now()	Returns the current date and time. Inputs None Return type datetime Example Now() → 2024-02-15 13:04:56
Today()	Returns the current date. Inputs None Return type datetime Example Today() → 2024-02-15

➕ Aggregates

Function	Description
Avg(number)	Computes the mean of the input column. Inputs number: A numeric column you want to compute the average of. Return type number Example Avg(column) → 2.53
Count(column)	Counts the number of all values in the input column. Inputs column: A column (of any type) whose values you want to count. Return type number Example Count(column) → 1640
Max(number)	Returns the maximum number in the input column. Inputs number: A numeric column you want to compute the maximum of. Return type number Example Max(column) → 5007
Median(number)	Returns the median number from the input column. Inputs number: A numeric column you want to compute the median of. Return type number Example Median(column) → 52
Min(number)	Returns the minimum number in the input column. Inputs number: A numeric column you want to compute the minimum of. Return type number Example Min(column) → -47
StdDev(number)	Returns the sample standard deviation of the input column. Inputs number: A numeric column you want to compute the sample standard deviation of. Return type number Example StdDev(column) → 1.43
StdDevPop(number)	Returns the population standard deviation of the input column. Inputs number: A numeric column you want to compute the population standard deviation of. Return type number Example StdDevPop(column) → 1.47
Sum(number)	Returns the sum of all the values in the input column. Inputs number: A numeric column whose values you want to take the sum of. Return type number Example Sum(column) → 387
Variance(number)	Returns the sample variance of the input column. Inputs number: A numeric column you want to compute the sample variance of. Return type number Example Variance(column) → 2.12
VariancePop(number)	Returns the population variance of the input column. Inputs number: A numeric column you want to compute the population variance of. Return type number Example VariancePop(column) → 2.42

Other Syntax

Syntax	Description	Example
()	Grouping, mostly used for order of operations on arithmetic.	(a+b)/c
``	Wrapper for column references. Only required if the column name contains special characters such as a whitespace.	`My Column`

Nulls

The result of a calculation will be null if null is one of the formula's arguments, and substituting the null argmument with different values would produce different outcomes. For example, startswith(null, 'Hello') returns null because substituting null with different values produces different outcomes (substituting with Hello produces True, while substituting with Hi returns False). Some more examples are below:

Example	Outcome
1 + null	null
If(null, b, c)	c
Switch(null, b, c)	null
True OR null	null
False OR null	null
True AND null	null
False AND null	False
null OR null	null
null AND null	null

Null values are ignored for all aggregate calculations. For example, Count(column) will ignore null values when calculating count of values in column.

Known limitations

• Not all databases support calculations with Query mode: It is not possible to configure calculations on query objects from certain connections. See the table below to understand which connections are supported.
• No windows: Window operations (Rank(), PercentOfTotal(), offset range inputs, etc.) are not currently supported.
• Column-level, not cell-level: Calculations happen on the column level. It is not possible to perform a calculation on a single row without applying it to the rest of the column. Similarly, you must reference whole columns in your formulas. It is not possible to reference an individual row, or range of rows, in your formula.

Supported connections

The below table details which data connections are supported when using Calculations on query objects.

Database	Supported?
AlloyDB	✅
Athena	✅
BigQuery	✅
ClickHouse	✅
CloudSQL (MySQL)	✅
CloudSQL (PostgreSQL)	✅
CloudSQL (MS SQL Server)	❌
Databricks	✅
Dremio	❌
DuckDB	✅
MS SQL Server	❌
MariaDB	✅
Materialize	❌
Motherduck	✅
MySQL	✅
PostgreSQL	✅
Presto	❌
Redshift	✅
Snowflake	✅
Starburst	❌
Trino	❌