Databases Reference
In-Depth Information
Aggregation functions
Of course, QlikView offers more aggregation options than summing. The most
commonly used options are shown in the following table:
Function
Explanation
Example
Sum()
Sums numeric expressions. Optionally a
DISTINCT
qualifier can be added, this will
cause the function to ignore duplicate values.
Sum(DISTANCE)
Sum(DISTINCT AIR_
TIME)
Min()
Returns the lowest value within a numeric
range. Optionally a rank can be specified,
this will return the nth lowest number. So
2
returns the second lowest number.
Min(DISTANCE)
Min(DISTANCE, 2)
Max()
Returns the highest value within a numeric
range. Optionally a rank can be specified,
this will return the nth highest number. So
2
returns the second highest number.
Max(PASSENGERS)
Max(PASSENGERS, 2)
Only()
If the aggregation of a value returns only a
single value, that value is returned, otherwise
the function returns null. For example, when
an expression contains the values {1, 1, 1}
then the
Only()
function will return
1
. If an
expression contains the values {
1
,
2
,
3
} then
the
Only()
function returns null.
Only(SEATS)
MinString
(MANUFACTURER)
MinString
()
Similar to the
Min()
function, but applied
to text strings. Also, it does not have the
optional rank parameter.
MaxString()
Similar to the
Max()
function, but applied
to text strings. Also, it does not have the
optional rank parameter.
MaxString
(MANUFACTURER)
Concat()
Concatenates all the values of an expression
into a single string, which is separated by a
delimiter given as a function's parameter.
Has an optional
DISTINCT
qualifier which
will set the function to ignore duplicate
values.
Concat(AIRPORT_
NAME, ';')
Concat(DISTINCT
MANUFACTURER, ',')
Count()
Counts the number of items in the input
expression. Has an optional
DISTINCT
qualifier that sets the function to ignore
duplicate values. Instead of an expression
an
*
(asterisk) can also be used to count the
number of rows.
Count(AIRCRAFT_
NAME)
Count(DISTINCT
AIRCRAFT_NAME)
