# Aggregation Functions in QlikView

This article provides an introduction to aggregation functions in QlikView.

**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

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.**

**min( expression[, rank] )**

Syntax

Syntax

**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

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.

**FirstValue(expression)**

Syntax

Syntax

**Load City, FirstValue(Name), as FirstName from abc.xls group by City;**

Example

Example

**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.

**LastValue(expression)**

Syntax

Syntax

**Load City, LastValue(Name), as LastName from abc.xls group by City;**

Example

Example

**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.

**concat ([ distinct ] expression [, delimiter [, sort-weight]])**

Syntax

Syntax

**Load Department, concat(Name,';') as NameList from abc.xls group by Department;**

Example

Example

Counter Aggregation Functions

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

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.

**NumericCount([ distinct ] expression )**

Syntax

Syntax

**Load Month, NumericCount(Item) as NumberOfNumericItems from abc.xls group by Month;**

Example

Example

**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 )

**Load Month, TextCount(Item) as NumberOfTextItems from abc.xls group by Month;**

Example

Example

**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 )

**Load Month, NullCount(Item) as NumberOfNullItems from abc.xls group by Month;**

Example

Example

**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.**

MissingCount()

MissingCount()

**Syntax**

MissingCount([ distinct ] expression )

**Load Month, MissingCount(Item) as NumberOfMissingItems from abc.xls group by Month;**

Example

Example

**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.