Skip to main content
Table calculations create ad hoc metrics that are performed post query processing on the result set, similar to Excel or Google Sheets. They can build contextual metrics like percent of row or column, period over period changes, or other calculations to customize analyses or visualizations.

​
Table calculations in Omni

Functions are predefined formulas designed to make calculations on values easier. For example:
NOW()           # Returns the current date and time, ex: 2025-05-09 18:23:30.521
TODAY()         # Returns the current date, ex: 2025-05-09
Some functions can accept arguments, which is the input or information provided to the function. In the following example, the arguments are 1, 2, 3:
SUM(1,2,3)         # Returns 6
Arguments can be several types, which can be thought of as the compositional building blocks of calculations:
  • String literals - Strings of text, which must be wrapped in double quotes. For example: "Hello, Blobby!"
  • Number literals - Numbers, such as 1 or 14. Can be used alone or as arguments.
  • Logical literals - Boolean values (TRUE, FALSE). Note: Must be all caps or the values will be interpreted as a cell reference.
  • Unary Operators / Negation - Negates (-) or adds (+) a value. Can be thought of as operators with only a right operand. For example: 
    -daily_budget     # Returns the negative value of daily_budget, ex: -500
    Note: The + operator can also be used as a unary. However, Omni typically ignores it because +1 and 1 are logically equivalent.
  • Cell references - Points to a specific cell in a dataset. The row can be omitted to reference only the column. Omni interprets cell references relative to the first row of data. Calculations will be applied to all following rows with the number changed to offset how many rows down you are. 
    C1                    # Reference column C in row 1C2 + 10               # Reference column C in row 2users.count + 10      # Reference users.count column
  • Cell range references - Compound cell reference that describes a span between one cell and another. Can be used to reference an entire column or a subset of the column’s data. Note: Cell ranges are inclusive. 
    C1:C5                 # Include cells C1,C2,C3,C4,C5C:C                   # Include all of column C
Refer to the Omni supported functions reference for a list of the functions Omni currently supports.
Omni aims to be compatible with Excel or Google Sheets wherever possible. The Google Sheets documentation included in the Omni supported functions reference is useful for enhancing your understanding of Omni’s functions.Note: For some functions, there are some differences between Omni’s implementation and Google Sheets’. Refer to the reference for the specific Omni function for more information.
There are some other functions you may see in the SQL that are not meant for end-user use, but help make the SQL blocks easier to parse in normal usage:The OMNI_PIVOT_ROW(dimensions) and OMNI_PIVOT(column_limit, pivots) structure the pivot table experience.
  • OMNI_PIVOT_ROW sets the query columns outside the pivot
  • OMNI_PIVOT sets the query columns to pivot, along with a limit on columns

​
Create table calculations

There are a few ways to create calculations in Omni:
  • Quick calculations, which allow you to create calculations like a running total or % of previous with just a few clicks
  • Using AI, which can help you get started quickly
  • Manually, which allows you to iterate and build at your own pace
Each approach can use all of Omni’s supported functions. Additionally, regardless of how the calculation is created, calculations can be renamed, formatted, and added to the workbook.
Before you get started:
  • Make sure that the query includes the fields you want to use in the calculation
  • Keep in mind that all alphabetic characters must be wrapped in double quotes (") unless referring to a specific cell or column. For example: "Hello, Blobby!". Single quotes are not valid for wrapping strings in Omni.

​
Quick calculations

Quick calculations, such as percent of total, can be used on numeric data in a workbook results table in the query builder or SQL editor. They can also be used across pivots. For example, a running total for a row. To create a quick calculation:
1
Click the icon in the header of a numeric column in the results table, then Calculations:
2
Select an option from the Calculations menu. A new column will be created in the table:
The new column will be named using the convention <column_name> + <quick_calculation>, but you can rename it if needed.

​
AI-generated

With Omni’s AI, you can write complex, Excel-like formulas using natural language. Having AI create calculated fields for you reduces the time you’d otherwise spend searching for the right syntax and iterating to get it just right.
1

Open the calculation prompt

In a workbook, click the icon in the left navigation or the calculations bar. Clicking the option in the left navigation will open the Query Helper, while using the calculations bar will open a small textbox:Workbook AI formula button
2

Describe the formula

In the text box, describe the formula you’d like to create. You can include the function to use if you know it, but otherwise, it’s helpful to be specific about what you’re trying to achieve. Refer to the Examples section to get some inspiration.If you’re not sure where to begin, click Get suggestions in the formula prompt window to generate a list of potential starting points:Formula suggestions list
3

Submit the prompt

When finished, click -> to submit the prompt to AI.If you need to rename the column, you can do so using the column header’s options menu or the table options.
Check out the Examples section to see some example prompts you can use to create calculations.

​
Manually-created calculations

You can also manually create calculations in the results table of a query:
1
Click the Add a column icon to first add a new column to the table. Note: The new column will be named Calculation by default, but you can rename it.
2
In a cell or the function editor, enter = to start building the calculation. Note: The calculation must start with an equals sign (=).
3
Build the calculation using Omni’s supported functions. The following image shows an example of a DATEDIF calculation, which was used to calculate the number of days between the Created At and Delivered At columns:
Check out the Examples section to see some of what’s possible, such as how to return nulls or reference column totals.

​
Rename and format calculations

In the Options for the Results table, you can rename or change the formatting for calculations. You can also rename a calculation by double-clicking the column header and entering a new name:

​
Promote table calculations

To reuse a calculation in a workbook, you can promote it to the workbook model. Click the icon in the calculation column’s header, then Add to workbook: Once added to the workbook model, you can then promote the calculation to the shared model.
Some limitations do exist depending on the type of calculation, such as calculations based on other calculations or those that include a range. This is because some calculations depend on the shape of a specific result set.For example, a seven-day moving average that works when data is grouped by day, but not if the grouping is by month.

​
Duplicate calculations

To create a copy of an existing calculation, click the icon in the calculation column’s header, then Duplicate. This creates a new column with a copy of the calculation, which you can then modify. This is useful when you want to create variations of an existing calculation, such as testing a formula change or creating similar calculations for different fields.

​
Examples

To return a null value, use 1/0 as the calculation. Nulls will display as blanks.
Using row and column totals can be effective for more complex calculations across rows or columns independently. These aggregates can be referenced in calculations when they are activated for queries, which you can do in the Options menu for the Results table.This example references:
  • Column total ${users.count:column_total}
  • Row total - ${users.count:row_total}
  • Grand total (columns and rows, lower right) - ${users.count:grand_total}
These can also be chained with other functions. For example:
# Percent of row

${users.count} * 100.0 / ${users.count:row_total}`.
To reference these totals within table calculations:
  • Column total <ROW_LETTER>_TOTAL
  • Row total <ROW_LETTER>_ROW_TOTAL
  • Grand total <ROW_LETTER>GRAND_TOTAL
Calculations can also contain free text - just leave out the equals sign when creating the calculation. When you don’t include the equals sign, Omni will not treat the cell’s value as a formula.To create this type of calculation, add a new column to the table and then enter text in the cells.Free-text values can be used to cross-reference columns or cells in other workbook tabs. To do this, create a new column, enter an equal sign, and ` (backtick) to pull up the tab names in the cell:

Using IFS, create a formula that uses the standard two letter abbreviations for US states. For District of Columbia, use DC as the abbreviation.

In the example query, the A column contains the full name of the state. For example, California or Pennsylvania.
Generated formula
=IFS(A1 = "Alabama", "AL", A1 = "Alaska", "AK", A1 = "Arizona", "AZ", A1 = "Arkansas", "AR", A1 = "California", "CA", A1 = "Colorado", "CO", A1 = "Connecticut", "CT", A1 = "Delaware", "DE", A1 = "Florida", "FL", A1 = "Georgia", "GA", A1 = "Hawaii", "HI", A1 = "Idaho", "ID", A1 = "Illinois", "IL", A1 = "Indiana", "IN", A1 = "Iowa", "IA", A1 = "Kansas", "KS", A1 = "Kentucky", "KY", A1 = "Louisiana", "LA", A1 = "Maine", "ME", A1 = "Maryland", "MD", A1 = "Massachusetts", "MA", A1 = "Michigan", "MI", A1 = "Minnesota", "MN", A1 = "Mississippi", "MS", A1 = "Missouri", "MO", A1 = "Montana", "MT", A1 = "Nebraska", "NE", A1 = "Nevada", "NV", A1 = "New Hampshire", "NH", A1 = "New Jersey", "NJ", A1 = "New Mexico", "NM", A1 = "New York", "NY", A1 = "North Carolina", "NC", A1 = "North Dakota", "ND", A1 = "Ohio", "OH", A1 = "Oklahoma", "OK", A1 = "Oregon", "OR", A1 = "Pennsylvania", "PA", A1 = "Rhode Island", "RI", A1 = "South Carolina", "SC", A1 = "South Dakota", "SD", A1 = "Tennessee", "TN", A1 = "Texas", "TX", A1 = "Utah", "UT", A1 = "Vermont", "VT", A1 = "Virginia", "VA", A1 = "Washington", "WA", A1 = "West Virginia", "WV", A1 = "Wisconsin", "WI", A1 = "Wyoming", "WY", A1 = "District of Columbia", "DC")
Results table
StateState Abbreviation
AlabamaAL
AlaskaAK
ArizonaAZ
ArkansasAR
……

Give me the domain from the email column.

In the example query, the A column contains an email address. For example, blobby@blobsrus.com.
Generated formula
=RIGHT(A1, LEN(A1) - FIND("@", A1))
Results table
EmailEmail Domain
blobby@blobsrus.comblobsrus.com
blobross@gmail.comgmail.com
bobbyparton@blobbyworld.coblobbyworld.co

Tell me if COLUMN_A is a weekday or a weekend.

If you receive an error for the above prompt, try this instead:

Tell me if COLUMN_A is a weekday or a weekend. If using WEEKDAY, only provide a date as an argument.

In the example query, the A column contains a timestamp.
Generated formula
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),"Weekend","Weekday")
Results table
DateWeekday or Weekend
2024-12-27 00:25:19.000Weekday
2024-09-30 13:11:47.000Weekend
2023-04-27 18:59:25.000Weekday
2022-05-15 18:02:33.000Weekday