Calculations (Beta)
Use Calculations to write spreadsheet-style formulas in tables.
This feature is in Beta. If you are interested in using Calculations in your workspace, or have any feedback for us, reach out to [email protected].
- 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 from both Table Displays and the Table output of SQL Cells. 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. You can now type your formula using the syntax in the language documentation below. To reference columns, simply start typing the column name or click the column in the table. The formula bar will suggest matching columns as you type. Use the arrow keys to move up and down the suggestions list, and select the desired column by hitting Tab or Enter.
Press Enter or ⌘ + Enter to run the new calculated column. The formula runs for every row down the whole column; it is not possible to run a formula in a single row.
To edit an existing calculated column, either click the fx icon in the column header or click the column’s header and click “Edit formula…”
Calculated columns can be filtered, formatted, sorted, etc. just like any other column.
Formulas
The Hex calculation language is designed to be familiar to any spreadsheet user. 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
To keep things simple, the calculation language presents only 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 | null |
Note that there are no datetime literals (besides null); datetime inputs to functions have to be column references.
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
| Function | Category | Description | Return Type | Example |
|---|---|---|---|---|
Concat(text_1, ..., text_n) | 📝 Text | 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. | Text | Concat('Jane', ' ', 'Doe') → ‘Jane Doe’ |
Length(text) | 📝 Text | Counts the number of characters in text. Inputs text: A text value to measure the length of. | Number | Length('Hex') → 3 |
Left(text, n) | 📝 Text | 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. | Text | Left('abcd', 3) → ‘abc’ |
Right(text, n) | 📝 Text | 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. | Text | Right('abcd', 3) → ‘bcd’ |
Lower(text) | 📝 Text | Converts a text value to lowercase. Inputs text: A text value to make lowercase. | Text | Lower('Jane Doe') → ‘jane doe’ |
Upper(text) | 📝 Text | Converts a text value to uppercase. Inputs text: A text value to make uppercase. | Text | Upper('Jane Doe') → ‘JANE DOE’ |
Contains(text, search_text) | 📝 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. | Boolean | Contains('abcd', 'abc') → True |
StartsWith(text, search_text) | 📝 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. | Boolean | StartsWith('abcd', 'abc') → True |
EndsWith(text, search_text) | 📝 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. | Boolean | EndsWith('abcd', 'bcd') → True |
Abs(number) | 🧮 Math | Computes the absolute value of number. Inputs number: A number to take the absolute value of. | Number | Abs(-3) → 3 |
Ceiling(number) | 🧮 Math | Rounds up number to the nearest integer. Inputs number: A number to round up. | Number | Ceiling(3.14) → 4 |
Floor(number) | 🧮 Math | Rounds down number to the nearest integer. Inputs number: A number to round down. | Number | Floor(3.14) → 3 |
Power(number, power) | 🧮 Math | 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. | Number | Power(3, 2) → 9 |
Sqrt(number) | 🧮 Math | Takes the square root of number. Inputs number: The number to take the square root of. | Number | Sqrt(9) → 3 |
Exp(power) | 🧮 Math | Raises the mathematical constant e to the power of power. Inputs power: The power to raise e by. | Number | Exp(1) → 2.718281828459045 |
Round(number) | 🧮 Math | Rounds number up or down to the nearest integer. Inputs number: The number to round. | Number | Round(3.14) → 3 |
If(condition, value_if_true, value_if_otherwise) | 🔀 Logical | 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. | Text, Number, Boolean, or Datetime | If(num % 2 = 0, 'Even', 'Odd') |
Switch(switch_value, if_matches_1, result_1, ..., if_matches_n, result_n) | 🔀 Logical | 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. | Text, Number, Boolean, or Datetime | Switch(status_code, 1, 'Processing', 2, 'Confirmed', 3, 'Shipped', 4, 'Delivered') |
Coalesce(value_1, ..., value_n) | 🔀 Logical | 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. | Text, Number, Boolean, or Datetime | Coalesce(null, 0, 42) → 0 |
IsFinite(number) | 🔀 Logical | Returns True if number is not null, not NaN, and not Inf. Otherwise, returns False. Inputs number: A number to check for being finite. | Boolean | IsFinite(42) → True |
IsOneOf(value, match_1, ..., match_n) | 🔀 Logical | 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. | Boolean | IsOneOf(1, 2, 3, 4) → False (1 is not equal to 2, 3, or 4) |
IsNull(value) | 🔀 Logical | Returns True if value is null, and False otherwise. Inputs value: A text, number, boolean, or datetime value to check for being null. | Boolean | IsNull(null) → True |
ToText(number) | 🪄 Casting | Converts number into a text data type. Inputs number: A number to convert to text. | Text | ToText(123) → ‘123’ |
ToNumber(text) | 🪄 Casting | Converts text into a number data type. Inputs text: A text value to convert to a number. | Number | ToNumber('123') → 123 |
ToDatetime(text) | 🪄 Casting | Converts text into a datetime data type. Inputs text: A text value to convert to a datetime. | Datetime | ToDatetime('2024-02-19') → 2024-02-19T00:00:00 |
ToBoolean(number) | 🪄 Casting | Converts number into a boolean data type, where 0 is False and 1 is True. Inputs number: A number to convert to a boolean. | Boolean | ToBoolean(0) → False |
Year(datetime) Quarter(datetime) Month(datetime) Day(datetime) Hour(datetime) Minute(datetime) Second(datetime) Millisecond(datetime) | 📅 Date & Time | Extracts the date part specified in the function name from datetime. Inputs datetime: A datetime value to extract from. | Number | Day('2024-02-14') → 14 |
TruncYear(datetime) TruncMonth(datetime) TruncDay(datetime) TruncHour(datetime) TruncMinute(datetime) TruncSecond(datetime) | 📅 Date & Time | Truncates datetime down to the nearest datepart as specified in the function name. Inputs datetime: A datetime value to be truncated. | Datetime | TruncMonth('2024-02-14 10:31:50') → 2024-02-01 00:00:00 |
Now() | 📅 Date & Time | Returns the current date and time. Inputs None | Datetime | Now() → 2024-02-15 13:04:56 |
Today() | 📅 Date & Time | Returns the current date. Inputs None | Datetime | Today() → 2024-02-15 |
Functions are canonically written in PascalCase (e.g. TruncDay(); not TRUNCDAY() or truncDay()), although the language itself is not case sensitive so you can type in whichever case you prefer.
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). Here are some more examples:
| 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 |
Current 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.
- Where calculations can be used: Calculations are not currently supported in Chart cells or Pivot cells.
- No aggregations or windows: The Hex calculation language only supports scalar operations right now; i.e., operations within a single row such as
+,*,Concat(), andIf(). This means there are not any aggregation functions (Sum(),Min(),Max(), etc.) or window-like operations (Rank(),PercentOfTotal(), offset range inputs, etc.). - 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 can only reference whole columns in your formulas. You cannot reference an individual row, or range of rows other than a whole column, in your formula.
- No time zone handling: The datetime data type does not contain time zone information and cannot convert time zones.
- No calculations on calculations: Calculations may not reference other calculated columns.
- Parameterization not supported: It is not currently possible to reference variables from your project inside of formulas.
Supported connections
The below table details which data connections are supported when it comes to 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 | ❌ |