In the pivot table, every cell displays an aggregated value of raw data records. An aggregation function determines how the data records are aggregated into the value.
This guide describes which aggregation functions are available in Flexmonster and how to use them.
Note If you are using the custom data source API, you can implement custom 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.
| Name | Value | Description |
|---|---|---|
| Sum | "sum" | Calculates a sum of raw data records
Live example. Can be applied to fields of "number" and "time" types. |
| Count | "count" | Counts a number of raw data records
Live example. Can be applied to fields of all types. |
| Distinct Count | "distinctcount" | Counts a number of unique raw data records
Live example. Can be applied to fields of all types. |
| Average | "average" | Calculates the average (arithmetic mean) of raw data records
Live example. Can be applied to fields of "number" and "time" types. |
| Median | "median" | Calculates the median of raw data records
Live example. Can be applied to fields of "number" and "time" types. |
| Product | "product" | Calculates the product of raw data records
Live example. Can be applied to fields of "number" and "time" types. |
| Min | "min" | Calculates the smallest of raw data records
Live example. Can be applied to fields of "number", "date string", "datetime", and "time" types. |
| Max | "max" | Calculates the largest of raw data records
Live example. Can be applied to fields of "number", "date string", "datetime", and "time" types. |
| Population StDev | "stdevp" | Calculates population standard deviation for raw data records
Live example. The population standard deviation formula is the following:
"number" and "time" types. |
| Sample StDev | "stdevs" | Calculates sample standard deviation for raw data records
Live example. The sample standard deviation formula is the following:
"number" and "time" types. |
| % of Grand Total | "percent" | Calculates the percentage of a value compared to the grand total
Live example. 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
Live example. 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
Live example. 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
Live example. 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
Live example. 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
Live example. 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). Note that the first row of each level will contain empty cells
Live example. 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). Note that the first column of each level will contain empty cells
Live example. 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). Note that the first row of each level will contain empty cells
Live example. 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). Note that the first column of each level will contain empty cells
Live example. 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
Live example. 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
Live example. 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. |
You can also limit the list of available aggregation functions:
You can choose an aggregation in the following ways:
To choose an aggregation function for a measure via UI, use the sigma icon in the Field List:
Note You can control the visibility of the sigma icon using the showAggregations option.
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.
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);