Calculated fields
Card calculated fields allow you to obtain results by combining one or more fields resulting from the queries made.
Basic calculated fields can be composed using basic arithmetic operators, such as addition (+), subtraction (-), multiplication (*), or division (/), between one or more fields.
Here are some examples:
- Calculation of amount with VAT: metric_field * 1.21
- Difference between two fields: metric_field1 - metric_field2
- Application of a fixed margin to an amount: metric_field + 100
- Calculation of a ratio between 2 fields: metric_field1 / metric_field2
Note that when including a field in a calculated field expression, it must be enclosed in curly braces { }. Following the previous examples, it would be {metric_field} * 1.21
It is often interesting to be able to have a comparison for a metric with respect to past periods. For example, if we are visualizing the amount of the sale monthly, and the business has a strong dependence on the calendar, we may be interested in comparing it with the amounts of the sale produced the previous year.
Biuwer includes a set of functions to compare a metric with previous periods, which you can use when composing card calculated fields:
- PREV_YEAR: Compares the specified metric to the previous year.
- PREV_QUARTER: Compares the specified metric to the previous quarter.
- PREV_MONTH: Compares the specified metric to the previous month.
- PREV_WEEK: Compares the specified metric to the previous week.
- PREV_DAY: Compares the specified metric to the previous day.
All these functions are called in the same way, and always taking into account a date field as a reference to make the comparison.
The syntax is as follows, using a metric field and a date field as a time reference. Remember that when including the fields they must be enclosed in braces { }. We use PREV_YEAR as an example:
General syntax:PREV_YEAR(metric_field, date_field)A real example:PREV_YEAR({f_sales_invoice.total_amount}, {f_sales_invoice.issue_date})
Comparisons with previous periods are very useful if they are configured correctly with the appropriate date levels, but may not be useful in other cases.
For example, if we configure a chart with a YEAR date level and configure a time comparison with respect to the previous DAY, the result obtained will not make much sense.
These are the specific functions to operate with numeric fields and values:
- ABS: returns the absolut value of a number. For example, ABS(-2.55) = 2.55
- CEILING: rounds the number to the nearest integer of equal or greater value. For example, FLOOR(2.5541) = 3
- DIV: returns the result of dividing a number, field, or expression (numerator) by another number, field, or expression (denominator). For example, DIV(4, 2.3) = 1.7391
- EXP: returns "e" number raised to the power of the given number. For example, EXP(2) = 7.389
- FLOOR: rounds a number to the nearest integer of equal or lesser value. For example, FLOOR(2.5541) = 2
- LN: returns the natural logarithm of a number. Returns Null if number is less than or equal to 0. For example, LN(11.256) = 2.4209
- LOG: returns the logarithm of a number for the given base. If the base value is omitted, base 10 is used. For example, LOG(11.256, 10) = 4.0514
- MAX: returns the maximum of the two arguments, which must be of the same type, including numeric fields and other calculated fields. You can also calculate the maximum of a field. For example, MAX(4,7) = 7
- MIN: returns the minimum of the two arguments, which must be of the same type, including numeric fields and other calculated fields. You can also calculate the minimum of a field. For example, MIN(4,7) = 4
- MOD: returns the module or remainder of a division (useful to know, for example, if a number is even or odd). For example, MOD(5, 2) = 1
- POWER: raises the number to the specified power. For example, POWER(3, 2) = 9
- ROUND: rounds numbers to a specified number of digits. The decimals argument specifies how many decimal points of precision to include in the final result. If decimals is omitted, number is rounded to the nearest integer. For example, ROUND(11.256, 1) = 11.3
- SIGN: returns the sign of a number (-1 if it's negative, 0 if it's zero or 1 if it's positive). For example, SIGN(11.256) = 1
- SQUARE: returns the square of a number. For example, SQUARE(5) = 25
- SQRT: returns the square root of a number. For example, SQRT(25) = 5
Other functions you can use in card calculated field expressions are listed below. In most cases they are self-descriptive. In the card editor in Biuwer, we provide the necessary parameters in each case:
- CONCAT: concatenation of strings of text
- IF: logical check of an expression, the typical IF THEN ELSE
- RUNNING_SUM: cumulative sum of a metric
- RUNNING_AVG: running average of a metric
- RUNNING_MIN: running minimum of a metric
- RUNNING_MAX: running maximum of a metric
- RUNNING_COUNT: running count of a metric
- MOVING_SUM: sum of a moving interval of a metric
- MOVING_AVG: average of a moving interval of a metric
- MOVING_MIN: minimum of a moving interval of a metric
- MOVING_MAX: maximum of a moving interval of a metric
- DIFF_FROM_FIRST: difference between the first value and current value of a metric
- DIFF_FROM_PREV: difference between the previous value and current value of a metric
- DIFF_FROM_NEXT: difference between the current value and the next value of a metric
- DIFF_FROM_LAST: difference between the current value and the last value of a metric
- PCT_DIFF_FROM_FIRST: percentage difference between the first value and current value of a metric
- PCT_DIFF_FROM_PREV: percentage difference between the previous value and current value of a metric
- PCT_DIFF_FROM_NEXT: percentage difference between the current value and the next value of a metric
- PCT_DIFF_FROM_LAST: percentaje difference between the current value and the last value of a metric
- PCT_FROM_FIRST: percentage of the first value and current value of a metric
- PCT_FROM_PREV: percentage of the previous value and current value of a metric
- PCT_FROM_NEXT: percentage of the current value and the next value of a metric
- PCT_FROM_LAST: percentage of the current value and the last value of a metric
- TOTAL: sums all the values of a metric
- PCT_OVER_TOTAL: percentage of each value of a metric with respect to the total of that metric
Last modified 7mo ago