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.

...

This is the simplest type of metric. It returns the sum (function_sum) of all basket amounts (property baskets.amount).

All available functions and operators are described below.

Code Block

title	Turnover metric object syntax

{
    "name": "turnover_metric",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "baskets.amount"
            }
        ]
    }
}

...

Key Type Optionality Description Constraints

type

string

Status


colour	Red
title	REQUIRED

dwh query property, dwh query function, or a number

[dwh query function, property, number, variable]

content

array

Status


colour	Red
title	REQUIRED

array of dwh property definitions (see the content.content table)

options

object

Status


colour	Green
title	optional

function options, allows to specify filters

...

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

only one of withoutFilters and acceptFilters keys can be specified

{datasetName}.{datasetProperty}

{datasetName}.*

*.*

acceptFilters

array

Status


colour	Yellow
title	VARIES

specifies dataset properties to be explicitly joined into the final query

array of dataset properties

only one of withoutFilters and acceptFilters keys can be specified

{datasetName}.{datasetProperty}

{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

...

Some metrics are not computable when aggregated to certain datasets. Say we have a 3-level hierarchical administrative units - districts, wards and postcodes. But the business data is available only up to the 2nd level - wards.

Drawio

contentId	644251843
simple	0
zoom	1
simpleinComment	0
inCommentcustContentId	0644251843
pageId	120336991	custContentId	644251843
diagramDisplayName	acceptAggregateBy.xml
lbox	1
contentVer	2
revision	2
baseUrl	https://clevermaps.atlassian.net/wiki
diagramName	acceptAggregateBy.xml
pCenter	0
aspect	undefined
width	401
links
tbstyle
height	131

...

Key Type Optionality Description Constraints

property

string

Status


colour	Red
title	REQUIRED

identifier of a dataset property, which the filter will be applied to

{datasetName}.{datasetProperty}

value

string

long

decimal

boolean

Status


colour	Yellow
title	VARIES

value, by which the property will be filtered

this key is polymorphic - it doesn't have only one type

it can also be a single value, or an array:

for the in operator, specify an array of values
for all the other operators, use single value

only one of value and query keys can be specified

query

object

Status


colour	Yellow
title	VARIES

query, by which the property will be filtered (see the content.content table)

only one of value and query keys can be specified

operator

object

Status


colour	Red
title	REQUIRED

the operator that will be used by the filter

see the available filterBy operators

`filterBy` examples

Code Block

title	filterBy syntax excerpt with string value and eq operator
collapse	true

    "filterBy": [
        {
            "property": "shops.type",
            "value": "partner",
            "operator": "eq"
        }
    ]

...

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_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 84

New Version 85

Key

`content.options`

`filterBy` examples

Conditional functions

Page Comparison

Versions Compared

Old Version 84

New Version 85

Key

content.options

filterBy examples

Conditional functions

`content.options`

`filterBy` examples