Calculated fields
Card calculated fields allow you to obtain results by combining one or more fields resulting from the queries made.
Last updated
Card calculated fields allow you to obtain results by combining one or more fields resulting from the queries made.
Last updated
When you configure a data query on a card in Biuwer, the fields have a set of attributes that determine their behavior. Furthermore, due to the versatility of the system by allowing multiple queries and relationships between them, it is important to know how the identification of the fields works and the behavior they will have.
In the following screenshot you can see an example of a list of fields available for assembling a visualization.
Each field includes the following information:
Field type: dimension if color is green or metric if color is blue
Data type: indicated by the initial icon. ABC for text fields, 123 for numeric fields, a calendar icon for date fields, or a -/+ icon for boolean fields.
Query number and field number: indicated as, for example, Q1.1, for the field in the first position of the first query. For example, the field identified as Q2.3 is the third position field in the second query. If the order of the fields within a query is changed, Biuwer updates all the fields involved, including expressions in fields automatically calculated by you.
Field alias: this is the alias of the field, which acts as the name on the card. If you hover over it with the mouse, the details of the dataset and the source field are shown.
Aggregation function: it is the aggregation function configured for metric type fields. The available values are: None, Sum, Avg, Count, Count distinct, Max and Min.
When understanding and correctly handling the syntax to be included in the calculated fields, it must be taken into account that a data field is going to be specified between curly braces { }, where the query number and the field number must appear, and optionally the name of the field can appear. If you prefer, for simplicity, you can remove the name of the field, although it also helps to identify what it is.
For example, a calculated field titled “Yearly change %” that returns a number and is to behave as a metric in the visualization may have the following expression:
What this expression does is take the value of field #4 of query #1, called “Yearly change” and divide it by 100, for each of its values. As you can see in the previous image, there is a DIV numerical function that allows you to obtain the same result, although in a different way. If DIV were used the expression would be: DIV({Q1.4 Yearly change}, 100)
To operate with calculated fields created on a card, the syntax is specific with the prefix CF (Calculated Field). For example, {CF.1 Calculated Field Name} would include the first calculated field. As we mentioned previously, the simplified option of this expression is {CF.1}.
When composing a calculated field, in the calculated fields editing dialog, a list of available fields is displayed, where each one is already shown with the appropriate syntax, and if you double click on one of them, the field to expression.
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.
PREV_PERIOD: compares the metric specified for the previous period value applied to the date field used to specify the time range (date filter applied). This option is dynamic and more flexible than the PREV_YEAR, PREV_QUARTER, PREV_MONTH, PREV_WEEK, PREV_DAY functions, since these always compare with a specific time interval, while PREV_PERIOD takes the date level of the applied filter (YEAR, QUARTER, MONTH, WEEK , DAY) and dynamically calculates the metric value at the previous date value.
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({Q1.1 total_amount}, {Q1.2 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 absolute 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, CEILING(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 the 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_VALUE: Returns the maximum of at least two indicated arguments, which must be of the same type, including numeric fields, other fields, and other calculated fields. The syntax to use this function is MAX_VALUE(expression, expression [,expression]), where two numerical expressions are necessary and you can optionally specify all the numerical expressions you want. For example, MAX_VALUE(100000, {Q1.3 Population}) will return line by line, the largest value between 100000 and the value of the Population field.
MIN_VALUE: Returns the minimum of at least two arguments, which must be of the same type, including numeric fields, other fields, and other calculated fields. The syntax to use this function is MIN_VALUE(expression, expression [,expression]), where two numerical expressions are necessary and you can optionally specify all the numerical expressions you want. For example, MIN_VALUE(100000, {Q1.3 Population}) will return line by line, the smallest value between 100000 and the value of the Population field.
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 are omitted, the 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
Operations involving any numerical aggregation allow obtaining totaled values from a set of original values. Aggregation functions allow you to apply various calculations, such as those described below:
AVG: obtains the average value, the mean, of the indicated metric type field. This aggregation function takes into account other dimension type fields involved to include the values that apply in each case in the calculation. The syntax to use this function is AVG(metric_field). For example, AVG({Q1.4 Number of clicks})
COUNT: obtains the count of the values that the indicated metric type field has, regardless of whether they are repeated or not. This aggregation function takes into account other dimension type fields involved to include the values that apply in each case in the calculation. The syntax to use this function is COUNT(metric_field). For example, COUNT({Q1.4 Number of clicks})
COUNT_DISTINCT: obtains the count of the values that the indicated metric type field has, taking into account only the different values. That is, if a value is repeated, it will count as a single value in the returned account. This aggregation function takes into account other dimension type fields involved to include the values that apply in each case in the calculation. The syntax to use this function is COUNT_DISTINCT(metric_field). For example, COUNT_DISTINCT({Q1.4 Number of clicks})
DIFF_FROM_FIRST: gets the difference between the first and current value of a metric. It is useful when the data series is ordered with a specific criterion and you want to know, for each value, its difference with respect to the first value of the numerical series. The syntax to use this function is DIFF_FROM_FIRST(metric_field). For example, DIFF_FROM_FIRST({Q1.4 Number of clicks})
DIFF_FROM_LAST: gets the difference between the current and last value of a metric. It is useful when the data series is ordered with a specific criterion and you want to know, for each value, its difference with respect to the last value of the numerical series. The syntax to use this function is DIFF_FROM_LAST(metric_field). For example, DIFF_FROM_LAST({Q1.4 Number of clicks})
DIFF_FROM_NEXT: gets the difference between the current and next value of a metric. It is useful when the data series is ordered with a specific criterion and you want to know, for each value, its difference with respect to the next value in the numerical series. The syntax to use this function is DIFF_FROM_NEXT(metric_field). For example, DIFF_FROM_NEXT({Q1.4 Number of clicks})
DIFF_FROM_PREV: gets the difference between the previous and current value of a metric. It is useful when the data series is ordered with a specific criterion and you want to know, for each value, its difference with respect to the next value in the numerical series. The syntax to use this function is DIFF_FROM_PREV(metric_field). For example, DIFF_FROM_PREV({Q1.4 Number of clicks})
MAX: obtains the maximum value of the values taken by the indicated metric type field. This aggregation function takes into account other dimension type fields involved to include the values that apply in each case in the calculation. The syntax to use this function is MAX(metric_field). For example, MAX({Q1.4 Number of clicks})
MIN: obtains the minimum value of the values taken by the indicated metric type field. This aggregation function takes into account other dimension type fields involved to include the values that apply in each case in the calculation. The syntax to use this function is MIN(metric_field). For example, MIN({Q1.4 Number of clicks})
MOVING_AVG: gets the moving average of the range specified for the metric. The syntax to use this function is MOVING_AVG(metric field, previous range, next range), where a metric is indicated, the number of records before the current one, and the number of records from the current one, to obtain the calculation window. For example, MOVING_AVG({Q1.4 Number of clicks}, 5, 0).
MOVING_MAX: gets the maximum value in the range specified for the metric. The syntax to use this function is MOVING_MAX(metric field, previous range, next range), where a metric is indicated, the number of records before the current one, and the number of records from the current one, to obtain the calculation window. For example, MOVING_MAX({Q1.4 Number of clicks}, 5, 0).
MOVING_MIN: gets the minimum value in the range specified for the metric. The syntax to use this function is MOVING_MIN(metric field, previous range, next range), where a metric is indicated, the number of records before the current one, and the number of records from the current one, to obtain the calculation window. For example, MOVING_MIN({Q1.4 Number of clicks}, 5, 0).
MOVING_SUM: gets the sum of the values in the range specified for the metric. The syntax to use this function is MOVING_SUM(metric field, previous range, next range), where a metric is indicated, the number of records before the current one, and the number of records from the current one, to obtain the calculation window. For example, MOVING_SUM({Q1.4 Number of clicks}, 5, 0).
PCT_DIFF_FROM_FIRST: gets the percentage difference between the first and current value of a metric. It behaves like the DIFF_FROM_FIRST function but returns a percentage. The syntax to use this function is PCT_DIFF_FROM_FIRST(metric_field). For example, PCT_DIFF_FROM_FIRST({Q1.4 Number of clicks})
PCT_DIFF_FROM_LAST: gets the percentage difference between the current and last value of a metric. It behaves like the DIFF_FROM_LAST function but returns a percentage. The syntax to use this function is PCT_DIFF_FROM_LAST(metric_field). For example, PCT_DIFF_FROM_LAST({Q1.4 Number of clicks})
PCT_DIFF_FROM_NEXT: gets the percentage difference between the current and next value of a metric. It behaves like the DIFF_FROM_NEXT function but returns a percentage. The syntax to use this function is PCT_DIFF_FROM_NEXT(metric_field). For example, PCT_DIFF_FROM_NEXT({Q1.4 Number of clicks})
PCT_DIFF_FROM_PREV: gets the percentage difference between the previous and current value of a metric. It behaves like the DIFF_FROM_PREV function but returns a percentage. The syntax to use this function is PCT_DIFF_FROM_PREV(metric_field). For example, PCT_DIFF_FROM_PREV({Q1.4 Number of clicks})
PCT_FROM_FIRST: gets the percentage of the first and current value of a metric. It behaves like the DIFF_FROM_FIRST function but returns a percentage. The syntax to use this function is PCT_FROM_FIRST(metric_field). For example, PCT_FROM_FIRST({Q1.4 Number of clicks})
PCT_FROM_LAST: gets the percentage of the current and last value of a metric. It behaves like the DIFF_FROM_LAST function but returns a percentage. The syntax to use this function is PCT_FROM_LAST(metric_field). For example, PCT_FROM_LAST({Q1.4 Number of clicks})
PCT_FROM_NEXT: gets the percentage of the current and next value of a metric. It behaves like the DIFF_FROM_NEXT function but returns a percentage. The syntax to use this function is PCT_FROM_NEXT(metric_field). For example, PCT_FROM_NEXT({Q1.4 Number of clicks})
PCT_FROM_PREV: gets the percentage of the previous and current value of a metric. It behaves like the DIFF_FROM_PREV function but returns a percentage. The syntax to use this function is PCT_FROM_PREV(metric_field). For example, PCT_FROM_PREV({Q1.4 Number of clicks})
PCT_OVER_TOTAL: obtains the percentage of each value of a metric with respect to the total of said metric. The syntax to use this function is PCT_OVER_TOTAL(metric_field). For example, PCT_OVER_TOTAL({Q1.4 Number of clicks})
RUNNING_AVG: gets the cumulative average of a metric. The syntax to use this function is RUNNING_AVG(metric_field). For example, RUNNING_AVG({Q1.4 Number of clicks})
RUNNING_COUNT: gets the running count of a metric. The syntax to use this function is RUNNING_COUNT(metric_field). For example, RUNNING_COUNT({Q1.4 Number of clicks})
RUNNING_MAX: gets the running maximum of a metric. The syntax to use this function is RUNNING_MAX(metric_field). For example, RUNNING_MAX({Q1.4 Number of clicks})
RUNNING_MIN: gets the running minimum of a metric. The syntax to use this function is RUNNING_MIN(metric_field). For example, RUNNING_MIN({Q1.4 Number of clicks})
RUNNING_SUM: gets the running sum of a metric. The syntax to use this function is RUNNING_SUM(metric_field). For example, RUNNING_SUM({Q1.4 Number of clicks})
SUM: obtains the sum of values of the indicated metric type field. This aggregation function takes into account other dimension type fields involved to include the values that apply in each case in the calculation. The syntax to use this function is SUM(metric_field). For example, SUM({Q1.4 Number of clicks})
TOTAL_SUM: obtains the sum of all the values of the indicated metric type field. The syntax to use this function is TOTAL_SUM(metric_field). For example, TOTAL_SUM({Q1.4 Number of clicks})
TOTAL_AVG: obtains the average of all the values of the indicated metric type field. The syntax to use this function is TOTAL_AVG(metric_field). For example, TOTAL_AVG({Q1.4 Number of clicks})
TOTAL_MIN: obtains the minimum of all the values of the indicated metric type field. The syntax to use this function is TOTAL_MIN(metric_field). For example, TOTAL_MIN({Q1.4 Number of clicks})
TOTAL_MAX: obtains the maximum of all the values of the indicated metric type field. The syntax to use this function is TOTAL_MAX(metric_field). For example, TOTAL_MAX({Q1.4 Number of clicks})
TOTAL_COUNT: obtains the count of all the values that the indicated metric type field has, regardless of whether they are repeated or not. The syntax to use this function is TOTAL_COUNT(metric_field). For example, TOTAL_COUNT({Q1.4 Number of clicks})
TOTAL_COUNT_DISTINCT: obtains the count of all the values that the indicated metric type field has, taking into account only the different values. That is, if a value is repeated, it will count as a single value in the returned account. The syntax to use this function is TOTAL_COUNT_DISTINCT(metric_field). For example, TOTAL_COUNT_DISTINCT({Q1.4 Number of clicks})
Calculation windows in MOVING_XXX functions. The operation of these functions is based on defining a calculation window, composed of a number of records or values of a metric, whose values are ordered according to the criteria that apply. How big is a calculation window? By default, the current value is always included in the window. To define how big or small this window is, you can use the number of records or values, before (using previous range) and after (using next range) the current one. This allows you to define windows centered, shifted to the right or to the left of each value. Some examples:
If previous range = 7 and next range = 0, then the window includes the 7 values before the current one and the current one. It is a window moved to the left.
If previous range = 3 and next range = 3, then the window includes the 3 values before the current one, the current one, and the 3 values after the current one. It is a centered window.
If previous range = 2 and next range = 4, then the window includes the 2 values before the current one, the current one, and the 4 values after the current one. It is a window moved to the right.
Working with dates is important to be able to convert formats, obtain parts of a date, create dates, obtain today's date, etc. The date functions allow a multitude of options, such as those described below:
DATE: gets a date from the expression passed as a parameter, which is typically text in the international standard date format, with the mask 'YYYY-MM-DD'. The syntax to use this function is DATE(expr). For example, DATE({Q1.3 Date as text}) or DATE('2024-04-17').
DATEPARSE: gets a date from the expression passed as a parameter, which has the format specified in the indicated mask. This function allows you to flexibly adapt to different date formats present in the data for each field. The syntax to use this function is DATEPARSE(date_mask, expr). For example, DATEPARSE('MM/DD/YYYY', {Q1.3 Date as text}) or DATEPARSE('MM/DD/YYYY', '04/17/2024').
MAKEDATE: gets a date from the specified year, month, and day values. The syntax to use this function is MAKEDATE(year, month, day). For example, MAKEDATE({Q1.1 Year}, {Q1.2 Month}, {Q1.3 Day}) or MAKEDATE(2024, {Q1.2 Month}, 1).
DATEADD: gets a date that is the result of adding the specified number of intervals to another date. This function allows you to obtain dates that are 1 year later, 4 weeks earlier or 15 days later than others. The syntax to use this function is DATEADD(date_level, interval, date). The options available for date_level are: 'year', 'quarter', 'month', 'week' and 'day'. The interval must be an integer, positive, zero, or negative. For the previous examples it would be: DATEADD('year', 1, {Q1.5 My date}), DATEADD('week', -4, {Q1.5 My date}) or DATEADD('day', 15, { Q1.5 My date})
DATEDIFF: gets the difference between the two specified date fields, at the specified date level. This function allows you to obtain the days, weeks, months, quarters or years between two dates. The syntax to use this function is DATEDIFF(date_level, date1, date2). The options available for date_level are: 'year', 'quarter', 'month', 'week' and 'day'. The returned result is an integer, positive, zero, or negative. For example, DATEDIFF('day', {Q1.5 My date}, {CF.1 Other calculated date})
DATENAME: gets the name of the specified part of the date. It is used to display names of months, days of the week, etc. The syntax to use this function is DATENAME(date_part, date). The options available for date_part are: 'year', 'quarter', 'month', 'week', 'weekday', 'dayofyear' and 'day'. The returned result is a text, for example, DATENAME('month', MAKEDATE(2024, 17, 1)) = 'April'
DATEPART: gets the number that corresponds to the specified part of the date. It is used to obtain, for example, the day or week of a date. The syntax to use this function is DATEPART(iso_date_part, date). The options available for iso_date_part are: 'year', 'quarter', 'month', 'week', 'weekday', 'dayofyear', 'day', 'iso-year', 'iso-week' and 'iso- weekday'. The returned result is a number, for example DATEPART('iso-week', {Q1.1 OrderDate})
DATETRUNC: obtains a new date, the result of modifying the date indicated as a parameter, to have the first day of the year, quarter, month, week or today, always at 12:00 AM (or 0:00 PM). It is used to obtain the start dates of known and regular intervals. The syntax to use this function is DATETRUNC(date_level, date). The options available for date_level are: 'year', 'quarter', 'month', 'week' and 'day'. The returned result is a date, for example DATETRUNC('month', {Q1.1 OrderDate})
YEAR: obtains the value corresponding to the year of the date indicated as a parameter. The syntax to use this function is YEAR(date). For example, YEAR({Q1.1 OrderDate})
QUARTER: obtains the value corresponding to the quarter of the date indicated as a parameter. The syntax to use this function is QUARTER(date). For example, QUARTER({Q1.1 OrderDate})
MONTH: obtains the value corresponding to the month of the date indicated as a parameter. The syntax to use this function is MONTH(date). For example, MONTH({Q1.1 OrderDate})
WEEK: obtains the value corresponding to the week of the date indicated as a parameter. The syntax to use this function is WEEK(date). For example, WEEK({Q1.1 OrderDate})
DAY: obtains the value corresponding to the day of the year of the date indicated as a parameter. The syntax to use this function is DAY(date). For example, DAY({Q1.1 OrderDate})
ISOYEAR: obtains the value corresponding to the year of the date indicated as a parameter, according to the ISO 8601 standard for years. The syntax to use this function is ISOYEAR(date). For example, YEAR({Q1.1 OrderDate})
ISOWEEK: obtains the value corresponding to the week of the date indicated as a parameter, according to the ISO 8601 standard for weeks. The syntax to use this function is ISOWEEK(date). For example, ISOWEEK({Q1.1 OrderDate})
TODAY: gets today's date. The syntax to use this function is TODAY()
ISDATE: obtains the boolean indicating with true or false if the date indicated as a parameter is actually a valid date. The syntax to use this function is ISDATE(date). For example, ISDATE({Q1.1 OrderDate})
The different masks you can build for the data are available here.
Differences between ISO and “non” ISO date functions. Functions labeled ISO take into account minor differences in date formats, as documented by the ISO 8601 standard. You can usually use functions not labeled ISO to take advantage of the date format you specify or the one with the data field, and apply the regional configuration that you have configured in Biuwer (date format, start day of the week). If the returned values are not correct, you can switch to the ISO variant of the function.
It is often necessary to make modifications, adjustments or operations with text strings. To do this, use one of the following functions:
CONCAT: obtains a text string resulting from concatenating at least two indicated arguments, which must be of the same type, including text strings, text fields and other calculated text fields. The syntax to use it is CONCAT(expression, expression [,expression]), where two text expressions are necessary and you can optionally specify all the text expressions you want.. For example: CONCAT('Country: ', {Q1.3 SalesTerritoryCountry})
When it is necessary to apply different expressions according to different logics, it is common to use the following functions:
IF: checks the logic of an expression, specified to return a value if the check is true and another value otherwise. This is the typical IF+THEN+ELSE construction with the IF(comparison expr, then expr, else expr) syntax. For example, IF({Q1.3 SalesTerritoryCountry} = 'Australia', 'AUS', 'OTHER')
IFNULL: is a function that allows you to check if an expression is the value NULL, in which case it returns another expression or value. The syntax to use it is IFNULL(expr, expr). For example: IFNULL({Q1.3 SalesTerritoryCountry}, 'No value')
