Analytic Functions

Analytic functions are similar to aggregate functions because both use the contents of multiple data input rows to calculate the result. Analytic functions use flexible conditions that are specified by the OVER(...) clause to order and group input so that specific rows may be part of the calculation for several output values. Arcadia supports several analytic functions that examine overlapping groupings of data.

Developer Notes:
  • Syntax for analytic functions is slightly different depending on the type of data connection used. Analytic functions are not available for connections to MySQL, SQLite, and MariaDB.
  • In the query execution order, analytic functions follow the WHERE and GROUP BY clauses. Therefore, rows that are filtered out by these mechanisms are excluded, and never become part of the analytic function data subset.
  • When using both analytic functions and ordering, the available ordering options include all fields that are on the shelves, less the fields that are on the Filters shelf. To create a visual that is sorted on the results of an analytic functions, be sure to also place the field used in the analytic function onto the Tooltips shelf, if it is not already there.
  • Use Enter or Edit Expression option for running analytic functions that are not automated within Arcadia Enterprise.

Arcadia explicitly supports the following analytic functions:

In addition to these, you may use the expression builder to specify other analytic functions. See Enter or Edit Expression.