Aggregate functions calculate a return value across all the items in a result set. Arcadia Instant provides optional aggregation operations on data values, such as summation, string concatenation, and common statistical measurements of data.

To add an aggregate function of change the selection for aggregates, 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 Aggregates menu.

    aggregate options when on shelf
  3. From the Aggregates menu, select one of the following options:

    • Sum is the summation over specified data.

      Returns the value of SUM([field]).

    • Count counts the records of specified data.

      Returns the value of COUNT([field]).

    • Approx Distinct Count returns the value of NDV([field]) function, 'number of distinct values'.

      Similar to COUNT(DISTINCT([field])). See Impala NDV Function.

      For Arcadia connections, you may change the calculations to get a significantly more precise value by using our proprietary NDVP([field]) function. You must edit the expression in Expression Editor to use NDVP() instead of NDV(). See NDVP().

    • Exact Distinct Count returns the value of COUNT(distinct([field])).

    • Minimum returns the value of MIN([field]).

    • Maximum returns the value of MAX([field]).

    • Average returns the value of AVG([field]).

    • Approx Median returns the value of APX_MEDIAN([field]).
    • Standard Deviation returns the value of STDDEV([field]).
    • Population Standard Deviation returns the value of STDDEV_POP([field]).
    • Variance returns the value of VARIANCE([field]).
    • Population Variance returns the value of VARIANCE_POP([field]).
    • String Concat returns the value of GROUP_CONCAT([field]).

      It is a single string representing the argument value concatenated together for each row of the result set. If specifying optional separator (usually comma followed by a space), it appears between each pair of concatenated values.

  4. A checkmark precedes the selected aggregate function.
  5. Click Refresh Visual to see the changes.

Fields that use an aggregation function have a green dot on the Aggregates menu.

To remove an aggregate from a field, click on the selected aggregate function; that removes the check mark.