The family of functions known as aggregation functions consists of functions that take multiple field values as their input and return a single result per group, where the grouping is defined by a chart dimension or a group by clause in the script statement.
Aggregation functions include
Most aggregation functions can be used in both the data load script and chart expressions, but the syntax differs.
Using aggregation functions in a data load script
Aggregation functions can only be used inside LOAD and SELECT statements.
Using aggregation functions in chart expressions
The parameter of the aggregation function must not contain other aggregation functions, unless these inner aggregations contain the TOTAL qualifier. For more advanced nested aggregations, use the advanced function Aggr, in combination with a specified dimension.
An aggregation function aggregates over the set of possible records defined by the selection. However, an alternative set of records can be defined by using a set expression in set analysis.
How aggregations are calculated
An aggregation loops over the records of a specific table, aggregating the records in it. For example, Count(<
If the aggregation function contains fields from different tables, the aggregation function will loop over the records of the cross product of the tables of the constituent fields. This has a performance penalty, and for this reason such aggregations should be avoided, particularly when you have large amounts of data.
Aggregation of key fields
The way aggregations are calculated means that you cannot aggregate key fields because it is not clear which table should be used for the aggregation. For example, if the field <
However, if you use the distinct clause, the aggregation is well-defined and can be calculated.
So, if you use a key field inside an aggregation function without the distinct clause, QlikView will return a number which may be meaningless. The solution is to either use the distinct clause, or use a copy of the key – a copy that resides in one table only.
For example, in the following tables,