Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

MEDIAN

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6

Description

This function calculates the median per group. Median can be applied to INT, FLOAT or DATE columns.

The median is the middle element of a group. If the group has an even number of elements, the upper value of the two middle values is taken as the median.

Syntax
MEDIAN ( table.column )
NULL handling

NULL values are ignored, meaning that they do not influence the result. If all values of a group are NULL, the result for this group is also NULL.

Examples

[1] Median of column with 3 rows.

Query

Column1

MEDIAN ( "Table"."Column" )

Input

Output

Table

Column : INT

1

2

3

Result

Column1 : INT

2

[2] Median of column with 4 rows. If a group has an even number of entries, the upper value of the two middle values is taken.

Query

Column1

MEDIAN ( "Table"."Column" )

Input

Output

Table

Column : INT

1

2

3

4

Result

Column1 : INT

3

[3] Get the median value by country.

Query

Column1

"Table"."Country"

Column2

MEDIAN ( "Table"."Values" )

Input

Output

Table

Country : STRING

Values : INT

'US'

3

'DE'

10

'DE'

5

'FR'

5

'US'

4

'US'

3

Result

Column1 : STRING

Column2 : INT

'DE'

10

'FR'

5

'US'

3

[4] Get the median value mixed with nulls, grouped by country.

Query

Column1

"Table"."Country"

Column2

MEDIAN ( "Table"."Values" )

Input

Output

Country : STRING

Values : INT

'FR'

10

'FR'

null

'DE'

null

Result

Column1 : STRING

Column2 : INT

'DE'

null

'FR'

10