Introduction
Aggregation functions are very important for QlikView applications. These type of functions can only be used within a field list for loadiing and used with group by clause statements. These functions can be used to formulate a calculation.
![]()
Types of aggregation functions
- Basic aggregation functions
- String aggregation functions
- Counter aggregation functions
- Advanced aggregation functions
Basic aggregation functions
There are the following types of basic aggregation functions:
- sum()
- min()
- max()
- only()
- mode()
- firstsortedvalue()
Sum()
This function returns a sum of expressions over a number of records as defined by a group by clause. If the distinct keyword occurs before the expression then all the duplicates will be discarded.
Syntax
sum([distinct]expression)
Example
Load Month, sum(Sales) as SalesPerMonth
from abc.xls group by month;
Min()
This function returns the minimum value of an expression encountered over a number of records as defined by a group by clause. By default the rank is 1, which is corresponds to the lowest value. If the rank is 3 then the second lowest value will be returned. If the rank is 3 then the third lowest value will be returned and so on.
Syntax
min( expression[, rank] )
Example
Load Month, min(Sales) as SmallestSalePerMonth from abc.xls group by Month;
Load Month, min(Sales, 2) as SecondSmallestSalePerMonth from abc.xls group by Month;
Max()
This function returns the maximum value of an expression encountered over a number of records as defined by a group by clause. By default the rank is 1, which corresponds to the highest value. If the rank is 3 then the second highest value will be returned. If the rank is 3 then the third highest value will be returned and so on.
Syntax
max( expression[, rank] )
Example
Load Month, max(Sales) as HighestSalePerMonth from abc.xls group by Month;
Load Month, max(Sales, 2) as SecondHighestSalePerMonth from abc.xls group by Month;
Only()
If the expression is over a number of records and it is defined by a group by clause then it contains only one numeric value then only that value is returned otherwise something else is returned.
Syntax
only(expression )
Example
Load Month, only(Price) as OnlyPriceSoldFor from abc.xls group by Month;
Mode()
This functions returns the mode value, in other words the most common value of the expression over a number of records in the data as defined by a group by clause. If more than one value that is equally common occurs then the value is returned. The Mode function returns numeric values as well as text values.
Syntax
mode(expression )
Example
Load Month, mode( ErrorNumber ) as MostCommonErrorNumber from abc.xls group by Month;
Load Month, mode( Product ) as ProductMostOftenSold from abc.xls group by Month;
Firstsortedvalue()
This function returns the first value of the expression sorted by corresponding sort-weight when the expression is executed over a number of records as defined by a group by clause. Sort-weight returns a numeric value where the lowest value will render the corresponding value of the expression to be sorted first.
Syntax
firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])
Example
Load Customer, firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.xls group by Customer;
String Aggregation Functions
There are the following types of string aggregation functions:
- MinString
- MaxString
- FirstValue
- LastValue
- Concat()
MinString()
This string function returns the first text value of an expression over a number of records in business data as defined by a group by clause. If no text value is found, then value is returned.
Syntax
MinString(expression )
Example
Load Month, MinString(Month) as FirstSalesMonth from abc.xls group by year;
MaxString
This string function returns the last text value of an expression over a number of records in business data as defined by a group by clause. If no text value is found, then value is returned.
Syntax
MaxString(expression )
Example
Load Month, MaxString(Month) as LastSalesMonth from abc.xls group by year;
FirstValue()
This script function returns the first value in the load order of an expression over a number of records in business data as defined by a group by clause. If no text value is found, then value is returned. This function is only available as a script function.
Syntax
FirstValue(expression)
Example
Load City, FirstValue(Name), as FirstName from abc.xls group by City;
LastValue()
This script function returns the last value in the load order of an expression over a number of records in business data as defined by a group by clause. If no text value is found, then value is returned. This function is only available as a script function.
Syntax
LastValue(expression)
Example
Load City, LastValue(Name), as LastName from abc.xls group by City;
Concat()
This script functions returns the aggregated string concatenation of all values of an expression iterated over a number of records in business data as defined by a group by clause statement. In this script function each value is separated by the string found in a delimiter. The order of concatenation is by sort-weight. Sort-weight returns a numeric value where the lowest value is sorted first. If distinct occurs before the expression, all duplicates will be discarded.
Syntax
concat ([ distinct ] expression [, delimiter [, sort-weight]])
Example
Load Department, concat(Name,';') as NameList from abc.xls group by Department;
Counter Aggregation Functions
There are the following types of counter aggregation functions:
- Count()
- NumericCount()
- TextCount()
- NullCount()
- MissingCount()
Count()
This function returns the count of an expression over a number of records in business data as defined by a group by clause. If the distinct occurs before the expression, all duplicates will be discarded.
Syntax
count([distinct ] expression | * )
Example
Load Month, count(Sales) as NumberOfSalesPerMonth from abc.xls group by Month;
Load Month, count(distinct Customer) as CustomerBuyingPerMonth from abc.xls group by Month;
LoadMonth, count(*) as NumberOfRecordsPerMonth from abc.xls group per Month;
NumericCount()
This function returns the numeric count of an expression over a number of records as defined by a group by clause. If the distinct occurs before the expression, all duplicates will be discarded.
Syntax
NumericCount([ distinct ] expression )
Example
Load Month, NumericCount(Item) as NumberOfNumericItems from abc.xls group by Month;
TextCount()
This function returns the text count of an expression over a number of records as defined by a group by clause. If the distinct occurs before the expression, all duplicates will be discarded.
Syntax
TextCount([ distinct ] expression )
Example
Load Month, TextCount(Item) as NumberOfTextItems from abc.xls group by Month;
NullCount()
This function returns the null count of an expression over a number of records as defined by a group by clause. If the distinct occurs before the expression, all duplicates will be discarded.
Syntax
NullCount([ distinct ] expression )
Example
Load Month, NullCount(Item) as NumberOfNullItems from abc.xls group by Month;
MissingCount()
This function returns the missing count of an expression over a number of records as defined by a group by clause. If the distinct occurs before the expression, all duplicates will be discarded.
Syntax
MissingCount([ distinct ] expression )
Example
Load Month, MissingCount(Item) as NumberOfMissingItems from abc.xls group by Month;
Advanced Aggregation function
The aggr function is known as an advanced aggregation function. This function returns a set of values expression calculated over dimensions. The result can be compared to the expression column of a chart, where the aggr function resides. Each chart has a dimension. The dimension is a single field. The dimension is not considered as an expression. The expression is a calculated dimension.
Syntax
aggr ([ distinct | nodistinct ] [{set_expression}]expression {,
dimension})
Examples
aggr( sum(Sales), Country )
aggr( nodistinct sum(Sales), Country )
aggr( sum(Sales), Country, Region )
count( aggr( sum(Sales), Country ))
Summary
This article provides an introduction to aggregation functions in QlikView and also describes various types of aggregation functions. I hope this is beneficial for you when you calculate dimensions in a chart as an expression in QlikView.