Aggregated Expression Functions
Summary
- This page lists all the Aggregated Expression Functions shipped by AdapTable
- These functions iterate over multiple valuues and return a single value (of any type)
AdapTableQL provides a large range of Aggregated Expression Functions.
These are available in Aggregation Expressions.
Standard Aggregations
AdapTable provides a large set of Aggregated Functions that can be used in Aggregated Expressions.
Hint
These Aggregation Functions are also used when displaying Cell Summaries and Row Summaries
Note
All these functions are Aggregation Scalar: recieving a Column (or array) as an input and returning a single value
| Function | Example | Returns | Columns |
|---|---|---|---|
| SUM | SUM([col1]) | Sum of the Column Values | Numeric |
| PERCENTAGE | PERCENTAGE([col1]) | Percentage of Column Values | Numeric |
| AVG | AVG([col1]) | Average of Column Values | Numeric |
| MIN | MIN([col1]) | Minimum value in Column | Numeric, Date |
| MAX | MAX([col1]) | Maximum value in Column | Numeric, Date |
| MEDIAN | MEDIAN([col1]) | Middle value in Column | Numeric |
| COUNT | COUNT([col1]) | Number of values in Column | All |
| MODE | MODE([col1]) | Most popular value in Column | All |
| DISTINCT | DISTINCT([col1]) | No. of distinct values in Column | All |
| ONLY | ONLY([col1]) | Value in Column if all distinct | All |
| STD_DEVIATION | STD_DEVIATION([col1]) | Standard Deviation for Column | Numeric |
| WEIGHT | AVG([c1], WEIGHT([c2])) | Used with AVG in Weighted Averages | Numeric |
GROUP_BY & WHERE
2 additional Expression Functions keywords are often used in conjunction with Aggregation Expressions:
| Function | Example | Description |
|---|---|---|
| GROUP_BY | SUM([col1],GROUP_BY([col2])) | Groups aggregation operation within rows that have the same value in the specified column |
| WHERE | SUM([col1])>5 WHERE [col2]='USD' | Narrows scope of Aggregated Expression |
Cumulative Aggregations
2 Expression Functions keywords are required when creating Cumulative Expressions:
| Function | Example | Description |
|---|---|---|
| CUMUL | CUMUL(SUM([col1])) | Performs cumulative aggregation (running total) with given aggregation operation over a provided column |
| OVER | CUMUL(SUM([col1]),OVER([col2])) | Defines accumulative dimension (order) for enclosing cumulative aggregation |
Quantile Aggregations
3 Expression Functions keywords are available when creating Quantile Expressions:
Hint
Each of these Quantile Aggregations can also be provided with a GROUP_BY function
| Function | Example | Description |
|---|---|---|
| QUANT | QUANT([col1], 4) | Enables aggregations to be placed into 'n' Quantile 'buckets' |
| QUARTILE | QUARTILE([col1]) | Enables aggregations to be placed into 4 Quantile 'buckets' |
| PERCENTILE | PERCENTILE([col1]) | Enables aggregations to be placed into 100 Quantile 'buckets' |