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.

UI 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
• % change from previous, % change from previous (row)
• running total, running total (row)
• rank, rank (row)

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

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

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

More

Coming soon!