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.

To use analytic functions, follow these steps.

  1. Click on the field you plan to modify, to open the Field Properties menu.

  2. Under Field Properties, click to expand the Analytic Functions menu.

    analytic function options when on shelf

  3. Select one of the following analytic functions, directly supported by Arcadia Enterprise:

    • Moving average returns a series of values of a succession of averages that are derived from successive overlapping segments of a constant size. In requesting this measurement, you must specify a number of preceding and following rows.
    • Percent of group returns a series of values that represent the proportionate measure within its group.
    • Percentage of previous returns a series of values that represent the change in the measure relative to the preceding data point.
    • Running total returns a series of values that are continually adjusted to take account of additional data points.

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

Related content: