This article will help you to

  • 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.

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:

  • Addition : +
  • 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.

Formulas

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

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 y–th 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 linear trend of column x.

Quadratic trend – trend_quad(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.

Did this answer your question?