1. Home
2. Building Widgets
3. Calculated metrics

# Calculated metrics

• Discover all existing formulas available in Reeport
• Learn how to use them to create custom metrics

A calculated metric allows you to populate a column using a custom calculation or a formula, for instance: It can be as simple as A*100, (A-B)/B, or A+B but it can also be based on statistical formulas.

Here is a simple example:

## Operators

Basic relations can be expressed with the use of operators. We have three types of operators : arithmetic, boolean and comparison operators.

### Arithmetic operators

Those are the operators that you can use for simple arithmetics, between a column and/or a number. Here are the operators available:

• Subtraction :
• Multiplication: *
• Division: /
• Power (exponentiation): **
• Integer division: //
• Modulus: %

### Comparison operators

Those are the operators that you can use to compare columns between each other, or a column and a numeric value, to produce a truth value (True or false, 0 or 1). These operators are useful when used with relations such as if_else. The operators available are:

• Equality: ==
• Less than: <
• Less or equal: <=
• Greater than: >
• Greater or equal: >=

### Boolean operators

Those are the operators that you can use to combine truth values. The operators available are:

• Logical and: and
• Logical or: or

### Range operator

Some relations (such as sum_lines, etc.) let you operate on any number of columns at once. To reference multiple columns in a relation, you can select multiple contiguous columns by using the column sign. You can also chain columns by separating them with commas.

For example, calling the relation sum_lines(A:E, G, Z:AE) will sum the lines of columns A, B, C, D, E, G, Z, AA, AB, AC, AD, AE.

Reordering referenced Columns

Be careful when reordering Columns that are referenced in calculated Metrics. Make sure you always double-check after doing so!

## Formulas

Complex relations can be executed with functions. Here is a list of all the existing functions in Reeport:

Formula nameFormula syntaxDescription

### Sum of column – sum(x)

Defines the sum of rows of column x. Results will be displayed on every row of the relation metric. ### Average of column – mean(x)

Defines the mean (aka average) of all rows of column x. Results will be displayed on every row of the relation metric. ### Median of column – median(x)

Defines the median of all rows of column x. Results will be displayed on every row of the relation metric.

### Percentile of column – percentile(x,y)

Defines the y-th percentile of column x. y represents a quantile (from 0 to 100), the default value is 50. 25 or 75 are usually good values for y. ### Minimum of column – min(x)

Defines the minimum value of column x. Results will be displayed on every row of the relation metric.

### Maximum of column – max(x)

Defines the maximum value of column x. Results will be displayed on each row of the relation metric.

### Floor of column – floor(x)

Returns the integer before the value of column x (exemple: 12.68 becomes 12)

### Ceil of column – ceil(x)

Returns the integer after the value of column x (exemple: 12.68 becomes 13)

### Numerous – numerous(x, y)

Finds the biggest value of column x and returns the corresponding value of column y. ### Check if minimum (conditional) – is_min(x)

Returns 0 or 1 (false or true). Assert if the value is the min value of the column x.

### Check if maximum (conditional) – is_max(x)

Returns 0 or 1 (false or true). Assert if the value is the max value of the column x.

### Threshold (conditional) – threshold(x, y)

Returns 0 or 1 (false or true). Assert if each value in column x is superior to the threshold y. ### If-else (conditional) – if_else(x, y, z)

For each row, if the value of column x is true, return the value of column y, else return value of column z

### Row-wise minimum – min_lines(r)

Returns row-wise minimum of a range of columns r. The range of column r can be selected using for example A:E ( all columns between A and E ) and/or A,E ( column A and column E ). ### Row-wise maximum – max_lines(r)

Returns row-wise maximum of a range of columns r. The range of column r can be selected using for example A:E ( columns between A and E ) and/or A,E ( column A and column E ).

### Row-wise sum – sum_lines(r)

Returns row-wise sum of a range of columns r. The range of column r can be selected using for example A:E ( columns between A and E ) and/or A,E ( column A and column E ).

### Row-wise average – mean_lines(r)

Returns row-wise mean (aka average) of a range of columns r. The range of column r can be selected using for example A:E ( columns between A and E ) and/or A,E ( column A and column E ).

### Row-wise median – median_lines(r)

Returns row-wise median of range of columns r. The range of column r can be selected using for example A:E ( columns between A and E ) and/or A,E ( column A and column E ).

### Row-wise percentile – percentile_lines(r, n)

Returns row-wise n-th percentile of 2 or more columns r. The range of column r can be selected using for example A:E ( columns between A and E ) and/or A,E ( column A and column E ). n represents a quantile (from 0 to 100), the default value is 50. 25 or 75 are usually good values for n.

### Minimum of previous values – min_past(x)

Returns the minimum value of values up to current value of column x. ### Maximum of previous values – max_past(x)

Returns the maximum value of values up to current value of column x.

### Average of previous values – mean_past(x)

Returns the mean (aka average) value of values up to current value of column x.

Median of previous values – median_past(x)

Returns the median value of values up to current value of column x.

Percentile of previous values – percentile_past(x, y)

Returns the yth percentile of values up to current value of column x.

### Temporal difference – diff(x)

Returns for each value of row of column x the difference with the value of (row – 1). ### Temporal division – divide(x)

Returns for each value of row of column x the division with the value of (row – 1).

### Temporal delayed difference – diff_delay(x, n)

Returns for each value of row in column x the difference with the value of (row – n).

### Temporal delayed division – divide_delay(x, n)

Returns for each value of row in column x the division with the value of (row – n).

### Cumulative sum – sum_cum(x)

Cumulative sum of column x. ### Percentage of maximum of column – percent_max(x)

Transforms for each value of column x, into percentage of maximum of column x. ### Percentage of minimum of column – percent_min(x)

Transforms for each value of column x, into percentage of minimum of column x.

### Percentage of sum of column – percent_sum(x)

Transforms for each value of column x, into percentage of sum of column x.

### Variation from maximum to minimum – percent_diff(x)

Transforms for each value of column x, into percentage between minimum and maximum of column x.

### Percentage of median of column – percent_median(x)

Transforms for each value of column x, into percentage of median of column x.

### Percentage of average of column – percent_mean(x)

Transforms for each value of column x, into percentage of mean of column x.

### Moving average of column – moving_mean(x, w)

Returns mobile mean (aka average) of column x with a window of size w. ### Moving minimum of column – moving_min(x, w)

Returns mobile min of column x with a window of size w.

### Moving maximum of column – moving_max(x, w)

Returns mobile max of column x with a window of size w.

### Moving median of column – moving_median(x, w)

Returns mobile median of column x with a window of size w.

### Linear trend – trend_linear(x)

Returns quadratic trend of column x.

### Logarithmic trend – trend_loglin(x)

Returns log-linear trend of column x. As a logarithm is applied on the data, we preprocess it so we don’t have any negative values : we replace all negative and null values by one thousandth of the mean of all positive values.

### Value for dimension – value_for_dimension(x)

Returns the value from a specific cell based on the dimension value. Note the double quotes around the dimension name. Updated on November 27, 2019