Need a special offer?Find out if your project fits.
+
All documentation
  • Introduction
  • Connecting to data source
  • Browser compatibility
  • Documentation for older versions
  • Aggregation functions

    An aggregation function is applied to a measure and determines how multiple data records are summarized and calculated into a value.

    Note If you are using the custom data source API, you can implement custom aggregations.

    Available aggregations

    The table below lists all aggregation functions supported by Flexmonster. To see which aggregations are available for each data source, refer to Flexmonster’s technical specifications.

    NameValueDescription
    Sum"sum"Calculates a sum of underlying values.
    Can be applied to fields of "number" and "time" types.
    Count"count"Calculates a number of underlying values.
    Can be applied to fields of all types.
    Distinct Count"distinctcount"Calculates a number of unique underlying values.
    Can be applied to fields of all types.
    Average"average"Calculates the average (arithmetic mean) of underlying values.
    Can be applied to fields of "number" and "time" types.
    Median"median"Calculates the median of underlying values.
    Can be applied to fields of "number" and "time" types.
    Product"product"Calculates the product of underlying values.
    Can be applied to fields of "number" and "time" types.
    Min"min"Calculates the smallest of underlying values.
    Can be applied to fields of "number", "date string", "datetime", and "time" types.
    Max"max"Calculates the largest of underlying values.
    Can be applied to fields of "number", "date string", "datetime", and "time" types.
    Population StDev"stdevp"Calculates population standard deviation for underlying values.
    Can be applied to fields of "number" and "time" types.
    Sample StDev"stdevs"Calculates sample standard deviation for underlying values.
    Can be applied to fields of "number" and "time" types.
    % of Grand Total"percent"Calculates the percentage of a value compared to the grand total.
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    % of Column"percentofcolumn"Calculates the percentage of a value compared to the column total.
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    % of Row"percentofrow"Calculates the percentage of a value compared to the row total.
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    % of Parent Column Total"percentofparentcolumntotal"Calculates the percentage of a value compared to the parent subtotal in columns.
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    If there is one field in the columns, this aggregation works exactly like the "percentofrow".
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    % of Parent Row Total"percentofparentrowtotal"Calculates the percentage of a value compared to the parent subtotal in rows.
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    If there is one field in the rows, this aggregation works exactly like the "percentofcolumn".
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    Index"index"Calculates the aggregated weighted average of a value. This shows the impact of each value within a dataset's context.
    The aggregated weighted average formula is the following: (value * grand total) / (row total * column total). The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    Difference of Column"differenceofcolumn"Calculates the difference between values in two adjacent cells of the same level. The calculation is done per column (from top to bottom).
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    Difference of Row"differenceofrow"Calculates the difference between values in two adjacent cells of the same level. The calculation is done per row (from left to right).
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    % Difference of Column"%differenceofcolumn"Calculates the percentage difference between values in two adjacent cells of the same level. The calculation is done per column (from top to bottom).
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    % Difference of Row"%differenceofrow"Calculates the percentage difference between values in two adjacent cells of the same level. The calculation is done per row (from left to right).
    The values are calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the values are calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    Running totals of Columns"runningtotalsofcolumn"Calculates running totals (cumulative sum) of values in column cells of the same level. The calculation is done from top to bottom.
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.
    Running totals of Rows"runningtotalsofrow"Calculates running totals (cumulative sum) of values in row cells of the same level. The calculation is done from left to right.
    The value is calculated based on the "sum" aggregation for fields of "number" and "time" types. For other field types, the value is calculated based on the "count" aggregation.
    Can be applied to fields of "string", "number", "month", "weekday", "date", and "time" types.

    Choosing an aggregation

    You can choose an aggregation in the following ways:

    • Choose via UI
    • Preset in the report
    • Apply using API calls

    Via UI

    To choose an aggregation function for a measure via UI, use the sigma icon in the Field List:

    Click the sigma icon and choose the necessary aggregation.

    Note You can control the visibility of the sigma icon using the showAggregations option.

    In the report

    To apply an aggregation to a measure in the slice, specify the name of an aggregation in the aggregation property:

    report: {
    dataSource: {
    filename: "https://cdn.flexmonster.com/data/data.csv"
    },
    slice: {
    measures: [
    {
    uniqueName: "Price",
    aggregation: "average"
    }
    ]
    }
    }

    Note If the aggregation is not specified, its default value is either "sum", "count", or "min", depending on the field's type.

    Using API calls

    To change an aggregation function applied to a measure, use the runQuery() method:

    const slice = pivot.getReport().slice;
    slice.measures[0].aggregation = "distinctcount";
    pivot.runQuery(slice);

    You can also limit the availability of aggregation functions:

    See also