Skip to main content

Table Calculations and Spreadsheet Formulas

Table calculations create ad-hod metrics that are performed post query processing on the result set à la Excel or Sheets. They can build contextual metrics like percent of row or column, period over period changes, or other calculations to customize analyses or visualizations.

Omni supports Excel-style table calculations. Excel-style functions can be used to create formulas that manipulate data and calculate strings and numbers. Omni's list of available functions in each category can be found below.

For more advanced SQL-based table calculations, check out Omni Table Calculations.

Preset Quick Functions​

Several functions are available as quick calculations on top of numerics in the result set. Quick calculations are only available on UI-generated result sets or 'SQL Super Powers' result sets. Quick calculations are also available across pivots when active (for example, row running total).

  • % of total, % of total (row)
  • % of previous, % of previous (row)
  • % change from previous, % change from previous (row)
  • running total, running total (row)
  • rank, rank (row)

How To...​

Calculate with Excel-style Functions​

  1. In a workbook, navigate to the "Add a column" icon or select the three dot menu that appears on the right-hand side of a column header. Note: In order to perform a caculation, be sure that the fields (dimensions or measures) being referenced in the table calculation are selected in the workbook query.
  1. Start a new calculation from the function editor or directly in the cell. The calculation must start with an = sign. All alphabetic characters must be wrapped in double quotes, e.g. "Hello world", unless the text is referring to a specfic cell or column. Single quotes are not valid strings in Omni (or Excel/Sheets).

Promote Table Calculations to the Model​

Calculations can be promoted to dimensions or measures for reuse into the workbook, then optionally into the shared model. Depending on the type of calculation some limitations will exist, i.e. calculations based on other calculations, calculations that include a range reference.

Calculate with Preset Quick Functions​

  1. In a workbook, select the three dot menu that appears on the right-hand side of the column header. Towards the bottom of the dropdown menu there are preset Calculations readily available to use with existing workbook results. Learn more about these calculations here.
  1. Alternatively, the same Calculations mentioned in the previous step are available as quick icons from the function editor.

Calculate with Calculations AI​

Calculations AI allows users to create table calculations with natural language inputs.

Metadata Sharing​

  • NO DATASET RESULTS ARE PASSED TO CHATGPT.
  • Omni only passes through the column IDs and how they map to a table i.e. {A: users.id, B:user.state, C:order.count} to Calculations AI.
  • If you would like to disable this feature, please email Omni at support@exploreomni.com.

How to use Calculations AI​

  1. In a workbook, navigate to the "Add a column" icon or select the three dot menu that appears on the right-hand side of a column header. Note: In order to perform a caculation, be sure that the fields (dimensions or measures) being referenced in the table calculation are selected in the workbook query.
  2. Under the new table calculation column, select the initial cell to start formula and then select the "Natural Langugage Formula".
  3. When prompted, add the desired formula in plain text. e.g. Multiply 'Order Total' with 'Order Count'

Watch a demo of the feature!

Add Free-text Values​

  1. In a workbook, navigate to the "Add a column" icon or select the three dot menu that appears on the right-hand side of a column header. Note: In order to perform a caculation, be sure that the fields (dimensions or measures) being referenced in the table calculation are selected in the workbook query.
  2. Under the new calculation column, add free-text values in the desired cells.
  3. 💥

Watch a demo of the feature!

Excel-style Functions​

Calculation Building Blocks​

Calculations consist of a handful of “primitive” types, which can be thought of as the compositional building-blocks of calculations.

String Literals​

String literals are just strings, you might use string literals as arguments in a concatenation function.

example: "Hello Omni"

notes:

  • String literals must be wrapped in double quotes.

Number Literals​

Number literals are just numbers.

example: 123

notes: - They can be used as function arguments or alone.

Logical Literals​

These are boolean values (true or false) values.

example: TRUE or FALSE

notes:

  • They must be all caps to be valid otherwise they’ll be interpreted as a field reference.

Unary Operators​

Negates a variable value.

syntax:: -value

example: -orders_sum

  • returns the negative value of the daily budget

notes:

  • Unary operators in a broad sense are operators with only a right operand.
  • In this example, the negative unary operator is used to express negation, such as “-1”.
  • The “+” operator can also be used as a unary, however we ignore it for the most part since “+1” and “1” are logically equivalent.

Binary Operators​

Binary operators are operators that take both a left and right operand.

example: value1 + value2

notes:

  • Expressions follow the standard order of operations. That is, the expression multiplies and divides before it adds and subtracts. You can use parentheses to clarify the expression or alter the order.
  • In the case of 1 + 2, “1” is the left operand, “+” is the binary operator and “2” is the right operand.
  • Supported binary operators: &, ``, +, -, /, <, <=, =, >, >=

Cell Reference​

A cell reference is a grid notation pointing to a specific cell in a data set.

example: C2

notes:

  • For example, C2 references the third column “C” and the second “2”.
  • The row can be omitted to reference the column. The column can also be referenced by name (ex: users.count)
  • Omni handles cell references by typically being in reference to the first row of data, and your calculation will be copied down to all following rows with the number changed to offset how many rows down you are.
  • For example, the calculation C1 + 10 will apply on the first row, but the second row will execute C2 + 10, the third row C3 + 10 and so on.

Cell Range Reference​

A compound cell reference that describes a span between one cell and another. Cell ranges are essentially arrays of data representing a subset of a given column.

example: C1:C5

notes:

  • Cell ranges are inclusive, so C1:C5 will include the following cells [C1,C2,C3,C4,C5]

Supported Functions​

Functions are predefined formulas designed to make calculations on values easier.

examples:

  • SUM(1,2,3)
  • NOW()

notes:

  • They accept “arguments” which are typically one of the other kinds of primitives, for example SUM(A1,B1,100) has two cell references and a number as its arguments.
  • We aim to be compatible with Excel or Sheets wherever possible. So, oftentimes, those docs are good for fleshing out your understanding of our functions. We have a few differences which will be noted below.

Math Functions​

ABS​

Returns the absolute value of a number.

syntax: ABS(x)

  • x is any numeric type field or value

example: ABS(-14)

notes:

AVERAGE​

Averages a list of numbers.

syntax: AVERAGE(list)

  • x is the list of numbers that can be composed of number literals (10), cell references (A1), cell ranges (B4:B9), and column references (C:C).

example: AVERAGE(1, 2, 3, 4)

notes:

CEILING​

Round numbers up.

syntax: CEILING(x, <significance-optional>)

example: CEILING(A1, .05)

notes:

COUNT​

Counts the number of cells that contain values in the column.

syntax: COUNT(x)

  • x is the cell references (E1), cell ranges (B4:B9), or column references (C:C).

example: COUNT(A1)

notes:

COUNTIF​

Counts the number of cells that contain values that meet a given criteria.

syntax: COUNTIF(cell_range, criteria)

  • cell_range – the range the criteria_argument is applied to
  • criteria – the test applied to the specified cell range

example: COUNTIF(A2:A5, A1 >= 2)

notes:

COUNTIFS​

Counts the number of cells that contain values that meet given criteria; this function supports multiple criteria arguments.

syntax: COUNTIF(cell_range, criteria1, [criteria2-optional])

  • cell_range – the range the criteria is applied to
  • criteria – the test criteria applied to the specified cell range

example: COUNTIFS(A1:A5, A1 > 2, B3:B10, B1 > A1)

notes:

FLOOR​

Round numbers down.

syntax: FLOOR(value, [significance-optional])

  • value – a number or cell reference could be inputted here
  • significance – input an optional significance value

example: FLOOR(A1, .05)

notes:

INT​

Rounds the value down to the nearest integer. Alias for FLOOR.

syntax: FLOOR(value, [significance-optional])

  • value – a number or cell reference could be inputted here
  • significance – input an optional significance value

example: FLOOR(A1, .05)

notes:

INTERCEPT​

Returns the intercept of the linear regression line through data points in X and Y data points.

syntax: INTERCEPT(x_value, y_value)

  • x_value: range of values representing the x-coordinate in a linear regression
  • y_value: range of values representing the y-coordinate in a linear regression

example: INTERCEPT(A1:A100, B1:B100)

notes:

LOG​

Returns the logarithm of a number to the base you specify.

syntax: LOG(number, [base-optional])

example: LOG(100, 10)

notes:

MAX​

Returns the largest number in a set.

syntax: MAX(range)

example: MAX(A1:A5)

notes:

MEDIAN​

Returns the median number in a set.

syntax: MEDIAN(range)

example: MEDIAN(B2:B8)

notes:

MIN​

Returns the smallest number in a set.

syntax: MIN(range)

example: MIN(C1:C10)

notes:

MOD​

Returns the remainder from division.

syntax: MOD(dividend, divisor)

example: MOD(10, 3)

notes:

MODE​

Returns the most common number in a set.

syntax: MODE(range)

example: MODE(A1:A5)

notes:

SLOPE​

Returns the slope of the linear regression line through data points in Ys and Xs.

syntax: SLOPE(x_value, y_value)

  • x_value: The range representing the array or matrix of dependent data.
  • y_value: The range representing the array or matrix of independent data.

example:

  • SLOPE (A1:A100, D1:D100)

notes:

STDEV​

Estimates the standard deviation based on a population of values.

syntax: STDEV(value)

  • value: value can be a number, range of values or an entire column

example:

  • STDEV(A1)
  • STDEV(A1:A5)

notes:

  • STDEV function only accepts one argument.
  • If a value argument references text values, Omni will return the calculation as null.
  • For additional information, refer to the Google Sheets STDEV() function documentation. Note, there is not full parity of functionality between the STDEV function in Google Sheets and Omni.

STDEV.P​

Estimates the standard deviation based on an entire population.

syntax: STDEV.P(value)

  • value: value can be a number, range of values or an entire column

example:

  • STDEV.P(A1)
  • STDEV.P(A1:A5)

notes:

  • STDEV.P function only accepts one argument.
  • If a value argument references text values, Omni will return the calculation as null.
  • For additional information, refer to the Google Sheets STDEVP() function documentation. Note, there is not full parity of functionality between the STDEV.P function in Google Sheets and Omni.

SUM​

Adds all the numbers in a range of cells. Ex: SUM(A1, B1:B5, …).

syntax: SUM(range)

example: SUM(A1:A5)

notes:

SUMIF​

Adds the cells specified by a given criteria. SUMIF(range, criteria, [sum_range]).

syntax: SUMIF(range, criteria, [sum_range-optional])

example: SUMIF(B2:B8, B2 > 5, C2:C8)

notes:

SUMPRODUCT​

Returns the sum of the products of corresponding array components. Ex: SUMPRODUCT(A1:A5, B:B, ...).

syntax: SUMPRODUCT(range1, range2, ...)

example: SUMPRODUCT(A1:A5, B1:B5)

notes:

TRUNC​

Truncates a number to an integer. Alias for FLOOR.

syntax: TRUNC(number)

example: TRUNC(8.9)

notes:

VALUE​

Converts a text argument to a number.

syntax: VALUE(text)

example: VALUE("123")

notes:

VAR​

Calculates the variance based on a sample of values.

syntax: VAR(value)

  • value: number or range of numbers

example:

  • VAR(1)
  • VAR(A1)
  • VAR(A2:A25)

notes:

  • VAR function only accepts one argument.
  • For additional information, see the Google Sheets VAR() function documentation.Note, there is not full parity of functionality between the VAR function in Google Sheets and Omni.

Trig Functions​

ACOS​

Returns the arccosine of a number.

syntax: ACOS(number)

example: ACOS(0.5)

notes:

ATAN​

Returns the Arctangent of a number.

syntax: ATAN(number)

example: ATAN(0.5)

notes:

COS​

Returns the cosine of a number.

syntax: COS(number)

example: COS(45)

notes:

COT​

Returns the cotangent of a number.

syntax: COT(number)

example: COT(45)

notes:

DEGREES​

Converts radians to degrees.

syntax: DEGREES(radians)

example: DEGREES(1.047)

notes:

Text Functions​

CONCAT or CONCATENATE​

Concatenates any number of strings.

syntax: CONCAT(string1, string2, ...)

example: CONCAT("Hello", " ", "World")

notes:

CLEAN​

Returns text with the non-printable ASCII characters removed.

syntax: CLEAN(text)

  • text: text that has non-printable characters that will be removed.

example: CONCAT("Hello"&CHAR(31))

notes:

FIND​

Returns the position of one string inside another.

syntax: FIND(find_text, within_text, [start_num])

example: FIND("n", "Omni")

notes:

LEFT​

Returns the specified number of characters from the start of a text string.

syntax: LEFT(text, [num_chars])

example: LEFT("Hello", 3)

notes:

LEN​

Returns the length of a string.

syntax: LEN(text)

example: LEN("Hello")

notes:

LOWER​

Converts text to lowercase.

syntax: LOWER(text)

example: LOWER("Hello")

notes:

MID​

Returns a specific number of characters from a text string starting at the position you specify.

syntax: MID(text, start_num, num_chars)

example: MID("Hello", 2, 3)

notes:

REPLACE​

Replaces characters within text.

syntax: REPLACE(old_text, start_num, num_chars, new_text)

example: REPLACE("Hello", 2, 3, "i")

notes:

Returns the specified number of characters from the end of a text string.

syntax: RIGHT(text, [num_chars])

example: RIGHT("Hello", 3)

notes:

Finds one text value within another (not case-sensitive).

syntax: SEARCH(find_text, within_text, [start_num])

example: SEARCH("n", "Omni")

notes:

T​

Converts its arguments to text.

syntax: T(value)

example: T(42)

notes:

TRIM​

Removes all spaces from text except for single spaces between words.

syntax: TRIM(text)

example: TRIM(" Hello World ")

notes:

UPPER​

Converts text to uppercase.

syntax: UPPER(text)

example: UPPER("Hello")

notes:

SUBSTITUTE​

Substitutes new_text for old_text in a text string.

syntax: SUBSTITUTE(text, old_text, new_text)

example: SUBSTITUTE("Hello", "o", "i")

notes:

Date & Time Functions​

DATE​

Creates a date from a day, month, and year.

syntax: DATE(year, month, day)

example: DATE(2022, 12, 22)

notes:

DATEDIF​

Find the difference between two dates.

syntax: DATEDIF(start_date, end_date, "unit")

example: DATEDIF(A1, B1, "M")

notes:

DAY​

Return the day of the month.

syntax: DAY(date)

example: DAY("2022-12-22")

notes:

DAYS​

Similar to DATEDIF. Finds the difference in days between two dates.

syntax: DAYS(start_date, end_date)

example: DAYS(A1, B1)

notes:

HOUR​

Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).

syntax: HOUR(time)

example: HOUR("15:30")

notes:

MINUTE​

Returns the minute as a number from 0 to 59.

syntax: MINUTE(time)

example: MINUTE("15:30")

notes:

NOW​

Returns the current date and time as a date-time value.

syntax: NOW()

example: NOW()

notes:

SECOND​

Returns the second as a number from 0 to 59.

syntax: SECOND()

example: SECOND()

notes:

TODAY​

Returns the current date as a date value. Does not accept arguments.

syntax: TODAY()

example: TODAY()

notes:

WEEKDAY​

Returns the day of the week as a number from 1 to 7.

syntax: WEEKDAY(date, [type])

example: WEEKDAY("2022-12-22", 2)

notes:

WEEKNUM​

Returns the week number of a specific date.

syntax: WEEKNUM(date, [type])

example: WEEKNUM("2022-12-22", 2)

notes:

YEAR​

Returns the year corresponding to a date.

syntax: YEAR(date)

example: YEAR("2022-12-22")

notes:

Logic Functions​

AND​

A logical AND, useful for IF statements and other logic work. It returns TRUE if all of its operands are TRUE.

syntax: AND(condition1, condition2, ...)

example: AND(A1 > 5, B1 < 10)

notes:

BITAND​

Bitwise AND operator.

syntax: BITAND(value1, value2)

example: [Provide example]

notes:

BITOR​

Bitwise OR operator.

syntax: BITOR(value1, value2)

example: [Provide example]

notes:

BITRSHIFT​

Bitwise right shift operator.

syntax: BITRSHIFT(value, shift_amount)

example: [Provide example]

notes:

BITXOR​

Bitwise XOR operator.

syntax: BITXOR(value1, value2)

example: [Provide example]

notes:

IF​

An “IF” statement checks a case, and if that’s TRUE it returns the second argument. If the case is FALSE, then it returns the third argument.

syntax: IF(condition, true_value, false_value)

example: IF(A1 > 10, "big", "small")

notes:

IFNA​

Returns the value you specify if the formula returns null; otherwise returns the result of the formula.

syntax: IFNA(value, default_value)

example: [Provide example]

notes:

IFS​

IFS is just like an IF statement except it can have multiple conditions.

syntax: IFS(condition1, value1, condition2, value2, ..., [default_value])

example: [Provide example]

notes:

ISBLANK​

Returns true if a value is blank.

syntax: ISBLANK(value)

example: [Provide example]

notes:

OR​

A logical OR, useful for IF statements and other logic work. It returns TRUE if one of its operands is TRUE.

syntax: OR(condition1, condition2, ...)

example: [Provide example]

notes:

Miscellaneous Functions​

RAND​

Generates a random number between 0 and 1.

syntax: RAND()

example: RAND()

notes:

Position Functions​

INDEX​

Returns a value or the reference to a value from an array.

syntax: INDEX(range, start_position)

  • range: the range of values to evaluate
  • start_position: the value of the position to start from to implement over the range of values

example:

  • INDEX(A2:A25, 12)

notes:

  • If you set row or column to 0, INDEX returns the array of values for the entire column or row, respectively.
  • For more examples, see Google Sheets INDEX() function documentation. Note, there is not full parity of functionality between the INDEX function in Google Sheets and Omni.

MATCH​

Returns the relative position of an item in a range that matches a specified value.

syntax: MATCH(search_arg, range)

  • search_arg: the value to compare the range of values against.
  • range: one-dimensional array of values to search.

example: MATCH("Jane", A10:A25)

notes:

  • MATCH returns the relative position in an array or range of a matched value rather than the matched value itself.
  • For more examples, see Google Sheets MATCH() function documentation. Note, there is not full parity of functionality between the MATCH function in Google Sheets and Omni.

PIVOT​

Returns a value from a specific pivot in the data table

PIVOTINDEX​

Returns the index of the current pivot.

PIVOTOFFSET​

Returns a pivot value offset from the current pivot column.

SWITCH​

Evaluates an expression against a list of values and returns the result corresponding to the first matching value.

syntax: SWITCH(expression, value1, result1, [value2, result2], ...)

example: SWITCH(A1, "apple", "fruit", "banana", "fruit", "carrot", "vegetable", "unknown")

notes:

  • The SWITCH function evaluates an expression against a list of values and returns the corresponding result.
  • Values and results are provided in pairs, and the first matching pair is returned.
  • The final pair is used as a default if no matches are found.
  • For more information, see Google Sheets SWITCH() function documentation.

Omni Table Calculations

Table calculations perform post processing calculations on the result set a la Excel or Sheets. They are performed after the pivottable query is run and can build contextual metrics like percent of row/column, period over period changes, or other post-processing to customize analysese or visualizations.

Advanced Functions​

In addition to the table-driven quick calculations, Omni has a rich library of table calculation functions that can be called in the SQL block. These will be lifted into a dedicated calculation experience (see below), but for now require touching the SQL block.

When calling calculations in SQL, functions will begin with an OMNI_ prefix, and can reference fields in the query using the ${} syntax:. This is a stopgap as we build the post-processing UI, where calculations will be written in-line (see below):

Coming soon.

Date Functions​

  • OMNI_DATE(time)

    • Calculate the date from a date or time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_DATE(${users.created_at}) = 2020-03-02

  • OMNI_DATETIME_INTERVAL_ADD(time, interval)

    • Adds an amount of time to another datetime
    • Expects [datetime, datetime_interval]
    • Examples: OMNI_DATETIME_INTERVAL_ADD(${users.created_at}, '7 days') = 2020-03-09

  • OMNI_DATETIME_LITERAL(string)

    • Converts a string into a datetime
    • Expects string formatted as a date or time
    • Examples: OMNI_DATETIME_LITERAL('2023-03-02') = 2020-03-02

  • OMNI_DATETIME_UNIT_INTERVAL_ADD(time)

    • This will add one unit to the given timeframe
    • Expects string formatted as a date or time
    • Examples: OMNI_DATETIME_UNIT_INTERVAL_ADD('2020') = 2021
    • Examples: OMNI_DATETIME_UNIT_INTERVAL_ADD('2020-03-02') = 2020-03-03
    • Examples: OMNI_DATETIME_UNIT_INTERVAL_ADD('2020-03-02 23:01:03') = 2020-03-02 23:01:04

  • OMNI_DAY_OF_MONTH(time)

    • Calculates the day of the month for a given date or time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_DAY_OF_MONTH(${users.created_at}) = 2

  • OMNI_DAY_OF_QUARTER(time, optional_fiscal_month_offset)

    • Calculates the quarter of the year for a given time, optionally using a fiscal calendar offset
    • Expects datetime or [datetime, fiscal_month_offset]
    • Examples: OMNI_DAY_OF_QUARTER(${users.created_at}) = 61

  • OMNI_DAY_OF_WEEK_INDEX(time, optional_week_start_day_offset)

    • Calculates the day of the week for a given time, optionally using a week start day offset
    • Expects datetime or [datetime, week_start_day_offset]
    • Expects ``
    • Examples: OMNI_DAY_OF_WEEK_INDEX(${users.created_at}) = 4

  • OMNI_DAY_OF_WEEK(time)

    • Calculates the day of the week for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_DAY_OF_WEEK(${users.created_at}) = Thursday

  • OMNI_DAY_OF_YEAR(time, optional_fiscal_month_offset)

    • Calculates the day of the year for a given time, optionally using a fiscal calendar offset
    • Expects datetime or [datetime, fiscal_month_offset]
    • Examples: OMNI_DAY_OF_YEAR(${users.created_at}) = 61

  • OMNI_FISCAL_QUARTER(time)

    • Calculates the quarter of the year for a given time, with a required fiscal calendar offset
    • Expects [datetime, fiscal_month_offset]
    • Examples: OMNI_FISCAL_QUARTER(${users.created_at}, 1) = 1

  • OMNI_FISCAL_YEAR(time)

    • Calculates the year for a given time, with a required fiscal calendar offset
    • Expects [datetime, fiscal_month_offset]
    • Examples: OMNI_FISCAL_YEAR(${users.created_at}, 1) = 2020

  • OMNI_HOUR_OF_DAY(time)

    • Calculates the hour of the day for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_HOUR_OF_DAY(${users.created_at}) = 23

  • OMNI_HOUR(time)

    • Calculates the hour (date + hour) for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_HOUR(${users.created_at}) = 2020-03-02 23

  • OMNI_MILLISECOND(time)

    • Calculates the millisecond (date + hour:minute:sec.millisecond) for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_MILLISECOND(${users.created_at}) = = 2020-03-02 23:01:03.123

  • OMNI_MINUTE(time)

    • Calculates the minute (date + hour:minute) for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_MINUTE(${users.created_at}) = 2020-03-02 23:01

  • OMNI_MONTH_NAME(time)

    • Calculates the month name for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_MONTH_NAME(${users.created_at}) = March

  • OMNI_MONTH_NUM(time, optional_fiscal_month_offset)

    • Calculates the month number for a given time, optionally using a fiscal calendar offset
    • Expects datetime or [datetime, fiscal_month_offset]
    • Examples: OMNI_MONTH_NUM(${users.created_at}) = 3

  • OMNI_MONTH(time)

    • Calculates the month (year-month) for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_MONTH(${users.created_at}) = 2020-03

  • OMNI_QUARTER_OF_YEAR(time, optional_fiscal_month_offset)

    • Calculates the quarter of the year for a given time, optionally using a fiscal calendar offset
    • Expects datetime or [datetime, fiscal_month_offset]
    • Examples: OMNI_QUARTER_OF_YEAR(${users.created_at}) = 3

  • OMNI_QUARTER(time)

    • Calculates the quarter (year-quarter) for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_QUARTER(${users.created_at}) = 2020-Q1

  • OMNI_SECOND(time)

    • Calculates the minute (date + hour:minute:second) for a given time
    • Expects timestamp, date, or datetime
    • Examples: OMNI_SECOND(${users.created_at}) = 2020-03-02 23:01:03

  • OMNI_WEEK(time)

    • Calculates the week (shown as the start date of the week) for a given time, optionally using a week start day offset
    • Expects datetime or [datetime, week_start_day_offset]
    • Examples: OMNI_WEEK(${users.created_at}) = 2020-02-27

  • OMNI_YEAR(time)

    • Calculates the year for a given time, optionally using a week start day offset
    • Expects datetime or [datetime, week_start_day_offset]
    • Examples: OMNI_YEAR(${users.created_at}) = 2020`

Logical Functions​

Coming soon.

Lookup Functions​

  • OMNI_OFFSET(cell, rows, columns, optional_length, optional_width)

    • Finds a corresponding cell down a column or across pivot
    • Pulls either a single value or an array with length and width
    • Expects [any_type, number, number, optional_number_greater_than_1, optional_number_greater_than_1]
    • Examples: OMNI_OFFSET(${orders.revenue}, 1, 0) = next_period_revenue
    • Examples: OMNI_OFFSET(${orders.revenue}, 1, 0, 5, 1) = an_array_of_next_5_periods_revenue

  • ABSOLUTE_POSITION(start_row)

This operator can be paired with the OMNI_OFFSET to support mixed calculation ranges. For example:

OMNI_OFFSET(${users.age}, ABSOLUTE_POSITION(4), 0, 1, 1)

  • This entails a rolling summation starting from the fourth row onward. Prior rows, up to the fourth, are "discarded", the fourth row consists of its own content, the fifth row has the content of rows 4 to 5, and so forth.

OMNI_OFFSET(${users.age}, 2, 0, ABSOLUTE_POSITION(100000), 1)

  • This represents a rolling accumulation extending beyond the end of the table, shifted by a factor of two. The final two rows remain empty, the third-to-last row contains the last row, the fourth-to-last row incorporates the third-to-last row, and so on.

OMNI_OFFSET(${users.age}, ABSOLUTE_POSITION(4), 0, ABSOLUTE_POSITION(48), 2)

  • From one absolute to another another absolute: every row contains the sum of values from rows 4 to 48.

Math Functions​

  • OMNI_PERCENT_CHANGE_FROM_PREVIOUS(cell)

    • Calculates the percent change between current row and previous row
    • Optional TRUE argument to calculate across a pivot, ie. OMNI_PERCENT_CHANGE_FROM_PREVIOUS(${users.count}, TRUE)
    • Will return null if there is no previous value or previous value is 0
    • Expects numeric
    • Examples: OMNI_PERCENT_CHANGE_FROM_PREVIOUS(${users.count}) = 11%

  • OMNI_PERCENT_OF_PREVIOUS(cell)

    • Calculates the percent the current row is as measured by the previous row
    • Optional TRUE argument to calculate across a pivot, ie. OMNI_PERCENT_OF_PREVIOUS(${users.count}, TRUE)
    • Expects numeric
    • Examples: OMNI_PERCENT_OF_PREVIOUS(${users.count}) = 111%

  • OMNI_PERCENT_OF_TOTAL(cell)

    • Calculates the perentage that the current row comprises of the given column
    • Optional TRUE argument to calculate across a pivot, ie. OMNI_PERCENT_OF_TOTAL(${users.count}, TRUE)
    • Expects numeric
    • Examples: OMNI_PERCENT_OF_TOTAL(${users.count}) = 2.4%

  • OMNI_RANK(cell)

    • Calculates the rank of the current row's value in it's given column
    • Optional TRUE argument to calculate across a pivot, ie. OMNI_RANK(${users.count}, TRUE)
    • Expects numeric
    • Examples: OMNI_RANK(${users.count}) = 12

  • OMNI_RUNNING_PRODUCT(cell)

    • Calculates the running product for a column up to the current row
    • Optional TRUE argument to calculate across a pivot, ie. OMNI_RUNNING_PRODUCT(${users.count}, TRUE)
    • Will error if there is a 0 in the column
    • Expects numeric
    • Examples: OMNI_RUNNING_PRODUCT(${users.growth}) = 125%

  • OMNI_RUNNING_TOTAL(cell)

    • Calculates the running total for a column up to the current row
    • Optional TRUE argument to calculate across a pivot, ie. OMNI_RUNNING_TOTAL(${users.count}, TRUE)
    • Expects numeric
    • Examples: OMNI_RUNNING_TOTAL(${users.count}) = 124,400

Text Functions​

Coming soon!

Referencing Totals​

Using row and column totals can be effective for more complex calculations across rows or columns indepdently. These aggregates can be referenced in calculations when they are activated for queries (using the table options menu).

  • 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 a percent of row, ${users.count} * 100.0 / ${users.count:row_total}.

Calculations can also be adjusted in the table options to rename or change formatting or decimals.

Internal Functions​

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.

  • OMNI_PIVOT_ROW(dimensions), OMNI_PIVOT(column_limit, pivots)
    • This pair of functions will structure the pivottable experience
    • OMNI_PIVOT_ROW sets the query columns outside the pivot
    • OMNI_PIVOT set the query columns to be pivotted, along with a limit on columns
    • example:

Table Calculation Examples​

Percent of Cohort - Gross Retention​

Often for these calculations we need to look across a given cohort / row and understand the percentage of the group that was retained. To do calculations across pivots, we can simply use a given cell and then the context of the row or column. Below we show a few examples with ${users.count}, and we can imagine they are grouped by ${users.sign_up[month]} and ${users.months_since_sign_up}

OMNI_PIVOT_OFFSET() offers some flexibility to use moving averages or lists. Here we divide each count by the max of the row, done using a column offset 100 columns back and 200 columns wide: 1.0 * ${users.count} / MAX(OMNI_OFFSET(${users.count}, 0, -100, 1, 200))

The alternative is using row_totals for simpler calculations:

1.0  ${users.count} / ${users.count:row_total}

Right now calculations cannot be pinned to the first column, so a window function would be required for % of first value, but this will open up soon.