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

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