1. Home
  2. Building Widgets
  3. Calculated metrics

Calculated metrics

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:

Reeport - Relation formulas

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:

Formula nameFormula syntaxDescription
Sum of columnsum()more info
Average of columnmean()more info
Median of columnmedian()more info
Percentile of columnpercentile()more info
Minimum of columnmin()more info
Maximum of columnmax()more info
Floor of columnfloor()more info
Ceil of columnceil()more info
Numerousnumerous()more info
Order of columnorder()more info
Check if minimum (conditional)is_min()more info
Check if maximum (conditional)is_max()more info
Threshold (conditional)threshold()more info
If/else (conditional)if_else()more info
Row-wise minimummin_lines()more info
Row-wise maximummax_lines()more info
Row-wise sumsum_lines()more info
Row-wise averagemean_lines()more info
Row-wise medianmedian_lines()more info
Row-wise percentilepercentile_lines()more info
Cumulative sumsum_cum()more info
Minimum of previous valuesmin_past()more info
Maximum of previous valuesmax_past()more info
Average of previous valuesmean_past()more info
Median of previous valuesmedian_past()more info
Percentile of previous valuespercentile_past()more info
Temporal differencediff()more info
Temporal divisiondivide()more info
Temporal delayed differencediff_delay()more info
Temporal delayed divisiondivide_delay()more info
Percentage of maximum value in columnpercent_max()more info
Percentage of minimum value in columnpercent_min()more info
Percentage of sum of columnpercent_sum()more info
Percentage of average of columnpercent_average()more info
Percentage of mean of columnpercent_mean()more info
Percentage of median of columnpercent_median()more info
Variation from minimum to maximumpercent_diff()more info
Moving-average of columnmoving_mean()more info
Moving-minimum of columnmoving_min()more info
Moving-maximum of columnmoving_max()more info
Moving-median of columnmoving_median()more info
Linear trendtrend_linear()more info
Quadratic trendtrend_quadratic()more info
Logarithmic trendtrend_loglin()more info
Value for dimensionvalue_for_dimensionmore info

Sum of column – sum(x)

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

Reeport - Relation sum

 

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.

Reeport - Relation mean

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.

Reeport - Relation percentile

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 12)

Numerous – numerous(x, y)

Finds the biggest value of column x and returns the corresponding value of column y.

Reeport - Relation numerous

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.

Reeport - relation threshold

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

Reeport - relation min_lines

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).
Reeport - relation diff

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.
Reeport - relation trend_linear
Reeport - Graph trend_linear

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.

Updated on September 1, 2017

Was this article helpful?

Related Articles