# Calculations (Beta)

Use Calculations to write spreadsheet-style formulas.

tip

Calculations are in Beta.

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.

## 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 TypePandas TypeSQL TypeExample Literals
TextobjectVARCHAR, CHAR'Hello!', '', null
Numberint64, float64INT, BIGINT, SMALLINT, FLOAT, DOUBLE, DECIMAL42, -42.0, 2.5e12, null
BooleanboolBOOLEANTrue, False, null
Datetimedatetime64DATE, DATETIME, TIMESTAMPnull

Note that there are no datetime literals (besides null); datetime inputs to functions must be column references.

## Operators​

OperatorDescriptionInput Data TypesReturn 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​

FunctionDescriptionExample
`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

### 🧮 Math​

FunctionDescriptionExample
`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​

FunctionDescriptionExample
`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​

FunctionDescription
`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​

FunctionDescription
`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)` `TruncMonth(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
`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​

FunctionDescription
`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​

SyntaxDescriptionExample
()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:

ExampleOutcome
1 + nullnull
If(null, b, c)c
Switch(null, b, c)null
True OR nullnull
False OR nullnull
True AND nullnull
False AND nullFalse
null OR nullnull
null AND nullnull

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.

DatabaseSupported?
AlloyDB
Athena
BigQuery
ClickHouse
CloudSQL (MySQL)
CloudSQL (PostgreSQL)
CloudSQL (MS SQL Server)
Databricks
Dremio
DuckDB
MS SQL Server