Page Comparison

A metric is an expression which describes a computation that will be performed over the project's data. Metrics are written in a custom query language, which is fairly simple and doesn't require users to know SQL. A metric written in this language is then translated to SQL by a backend service and executed as multidimensional query on data warehouse.

...

Key Type Optionality Description Constraints

id

string

Status


colour	Green
title	optional

choose a custom string identifier for the query property

(a-z0-9_-)

type

string

Status


colour	Red
title	REQUIRED

dwh query property, dwh query function, or a number

[dwh query function, property, number, variable]

value

string

long

decimal

Status


colour	Red
title	REQUIRED

string identifier of a dataset property, which the function will be applied to (for type=function)

string with variable name (for type=variable)

long or decimal value (for type=number)

{datasetName}.{datasetProperty}

`content.options`

Key Type Optionality Description Constraints

aggregateBy

array

Status


colour	Green
title	optional

specifies datasets which the metric will be aggregated to

if specified, always null

[null]

acceptAggregateBy

array

Status


colour	Yellow
title	VARIES

specifies datasets which the metric is allowed to be aggregated to

array of dataset names, or name prefixes (with the * wildcard)

only one of acceptAggregateBy and dontAggregateBy keys can be specified

{datasetName}

{datas*}

dontAggregateBy

array

Status


colour	Yellow
title	VARIES

specifies datasets which the metric is not allowed to be aggregated to

array of dataset names, or name prefixes (with the * wildcard)

only one of acceptAggregateBy and dontAggregateBy keys can be specified

{datasetName}

{datas*}

withoutFilters

array

Status


colour	Yellow
title	VARIES

specifies dataset properties not to be explicitly joined into the final query

array of dataset properties. The syntax from:{datasetName} is expanded to all datasets following given {datasetName} including the {datasetName} itself.

only one of withoutFilters and acceptFilters keys can be specified

{datasetName}.{datasetProperty}

{datasetName}.*

*.*

from:{datasetName}

acceptFilters

array

Status


colour	Yellow
title	VARIES

specifies dataset properties to be explicitly joined into the final query

array of dataset properties. The syntax from:{datasetName} is expanded to all datasets following given {datasetName} including the {datasetName} itself.

only one of withoutFilters and acceptFilters keys can be specified

{datasetName}.{datasetProperty}

{datasetName}.*

*.*

from:{datasetName}

filterBy

object

Status


colour	Green
title	optional

object specifying the filter of the metric

places

integer

Status


colour	Green
title	optional

the number of places to round the metric result to

only for a metric with function_round

...

Identifier	Description	Example
`function_avg`	arithmetic average of all input values	`function_avg(42.0, 17.38, 87.2, 36.9) = 45,87`
`function_sum`	sum of all input values	`function_sum(42.0, 17.38, 87.2, 36.9) = 183,48`
`function_count`	distinct count of all input values that are not `null`	`function_count("apple", "banana", "apple", "orange", "banana") = 3`
`function_max`	maximum value	`function_max(42.0, 17.38, 87.2, 36.9) = 87.2`
`function_min`	minimum value	`function_min(42.0, 17.38, 87.2, 36.9) = 17.38`
`function_stddev_samp`	sample standard deviation of the input values	`function_stddev_samp(42.0, 17.38, 87.2, 36.9) = 29.525620512813`
`function_stddev_pop`	population standard deviation of the input values	`function_stddev_pop(42.0, 17.38, 87.2, 36.9) = 25.569937426595`
`function_var_samp`	sample variance of the input values	`function_var_samp(42.0, 17.38, 87.2, 36.9) = 871.76226666667`
`function_var_pop`	population variance of the input values	`function_var_pop(42.0, 17.38, 87.2, 36.9) = 653.8217`
`function_percent_to_total`		`function_percent_to_total(42.0) = 653.8217`

Window functions

Window functions provide the ability to perform calculations across sets of rows that are related to the current query row.

Identifier	Description	Example
`function_ntile`	integer ranging from 1 to the argument value, dividing the partition as equally as possible	`input: buckets = 3, values = [15, 73, 54, 33, 24, 80], argument = 73, sort = "asc"` `input values are grouped into these buckets = ([15, 24], [33, 54], [73, 80])` `result: 3 (argument 73 is in the 3th bucket)`
`function_rank`	rank of the current row with gaps	`input: values = [15, 73, 54, 33, 24, 15], argument = 54, sort = "asc"` `input values sorted = (15, 15, 33, 54, 73, 80)` `result: 4 (argument 54 is the 4th element in sorted list of input values)`
`function_percentile`	value below which a given percentage of observations in a group of observations fall	`input: values = [10, 75.2, 35.2, 21, 42.7, 61.1, 105.9], argument = 75.2, sort = "desc"` input values sorted = (105.9, 75.2, 61.1, 42.7, 35.2, 21, 10) `result: 83,3 (number 75.2 is higher than 83,3% of other input values)`
`function_row_number`	a unique number for each row starting with 1. For rows that have duplicate values, numbers are arbitrary assigned.	`input: values = [15, 73, 54, 33, 24, 15], argument = 54, sort = "asc"` `input values sorted = (15, 15, 33, 54, 73, 80)` `result: 4 (argument 54 is the 4th element in sorted list of input values)`
`function_percent_to_total`	contribution to the total	`input: all values = [10, 75.2, 35.2, 21, 42.7, 61.1, 105.9], current group = 75.2` `result: 0.21 (group 75.2 has a contribution 21% to the total)`

Arithmetic functions

Basic arithmetic functions.

function_plus, function_minus and function_multiply accept 2 or more arguments
function_divide and function_modulo accept exactly 2 arguments

Identifier	Example
`function_plus`	`7 + 2 = 9`
`function_minus`	`7 - 2 = 5`
`function_multiply`	`7 * 2 = 14`
`function_divide`	`7 / 2 = 3.5`
`function_modulo`	`7 mod 2 = 1`

...

Identifier	Description	Example
`function_round`	a rounding function (round to specific number of places)	`function_round(56.157094235, 1) = 56.2`

Conditional functions

Identifier Description Example

function_ifnull

defines the default value to use if the result of aggregate function is null

number = 0.0, value = null

function_ifnull(null) = 0.0

...

Identifier	Description	Example
`eq`	is equal	`input values: ["apple","orange","banana"], eq = "orange"` `result: ["orange"]`
`ne`	negated	`input values: ["apple","orange","banana"], ne = "orange"` `result: ["apple","banana"]`
`in`	in range	`input values: [1,2,3,4,5,6,7,8], in = [3,6]` `result: [3,4,5,6]`
`lt`	lower than	`input values: [1,2,3,4,5,6,7,8], lt = 5` `result: [1,2,3,4]`
`lte`	lower than or equal	`input values: [1,2,3,4,5,6,7,8], lte = 5` `result: [1,2,3,4,5]`
`gt`	greater than	`input values: [1,2,3,4,5,6,7,8], gt = 5` `result: [6,7,8]`
`gte`	greater than or equal	`input values: [1,2,3,4,5,6,7,8], gt = 5` `result: [5,6,7,8]`
`isNull`	is null	`input values: [1,2,3,4,null,6,7,8], isNull = 2` `result: false`
`isNotNull`	is not null	`input values: [1,2,3,4,null,6,7,8], isNotNull = 2` `result: true`
`inAttribute`	is in attribute	see the usage in a complete metric example above
`notInAttribute`	is not in attribute	complementary to the `inAttribute` example

...

Versions Compared

Old Version 91

New Version 92

Key

`content.options`

Window functions

Arithmetic functions

Conditional functions