Table Calculations

Table calculations perform mathematical calculations on the fields in your query. They can be accessed by right-clicking the table header of a measure.

UI Functions

Several calculations are available on top of numerics in the result set. Quick calculations are only available on UI-generated result sets at the moment. Quick calculations are also available across pivots when active (for example, row running total).

• % of total
• % of previous
• % change from previous
• running total
• rank

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, but for now require touching the SQL block.

Functions will begin with an OMNI_ prefix, and can reference fields in the query using the ${} syntax:

Most Popular Functions

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

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}.

Note formatting is not available for calculations yet, so to format as percentages, you may want to multiply by 100 per above.

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

Coming soon.