Aggregate
Aggregate functions perform a calculation on multiple values to return a single result.
The following functions are available in Qlik Open Lakehouse:
| Function | Description |
|---|---|
| APPROX_COUNT_DISTINCT | Approximates the number of distinct non-null input values. |
| APPROX_COUNT_DISTINCT_EACH | Approximates the number of distinct values for each given group. |
| AVG | Computes the average (arithmetic mean) of all input values. |
| AVG_EACH | Computes the average (arithmetic mean) of all input values for each given group. |
| AVG_TIME_SERIES | Gets the average value per time interval. |
| COLLECT_SET | Collects a set of all values for a given field. |
| COLLECT_SET_EACH | Collects a set of all values for a field for each given group. |
| COUNT | Counts the number of non-null input values. |
| COUNT(*) | Counts the number of input rows. |
| COUNT(DISTINCT ...) | Counts the number of distinct non-null input values. |
| COUNT_EACH | Counts the number of items for each given group. |
| COUNT_IF | Counts the number of `TRUE` input values. |
| DYNAMIC_SESSIONS | Collects an array of session start and end times. |
| FIRST | Gets the first input value to arrive for each group. |
| FIRST_ARRAY | Gets the first array of values seen in the events within the time window. |
| FIRST_EACH | Gets an array of the first values per given group in the time window. |
| FIRST_TIME_SERIES | Gets the first value per time interval. |
| LAST | Gets the last input value to arrive for each group. |
| LAST_ARRAY | Gets the last array of values seen in the events within the time window. |
| Syntax | Gets an array of the last values per group in the time window. |
| LAST_K | Gets the last `k` values per group. |
| LAST_K_EACH | Gets the last `K` values per given group. |
| LAST_TIME_SERIES | Gets the last value per time interval. |
| MAX | Computes the maximum of all input values. |
| MAX_BY | Gets the value that corresponds to the maximum value of a different field in the time window. |
| MAX_EACH | Collects the maximum value per given group. |
| MAX_TIME_SERIES | Gets the maximum value per time interval. |
| MIN | Computes the minimum of all input values. |
| MIN_BY | Gets the value that corresponds to the minimum value of a different field in the time window. |
| MIN_EACH | Collects the minimum value per given group. |
| MIN_TIME_SERIES | Gets the minimum value per time interval. |
| SESSION_COUNT | Counts the number of sessions. |
| STD_DEV | Computes the standard deviation of a set of numbers. |
| STD_DEV_EACH | Computes the standard deviation of a set of numbers per given group. |
| STRING_MAX | Computes the maximum of all input string values. |
| STRING_MAX_EACH | Collects the maximum string value per given group. |
| STRING_MIN_EACH | Collects the minimum string value per given group. |
| SUM | Computes the sum of all input values. |
| SUM_EACH | Computes the sum of all input values for each given group. |
| SUM_TIME_SERIES | Gets the sum of all values per time interval. |
| WEIGHTED_AVERAGE | The weighted average of a field in the time window. |