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.