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
These functions are useful to know more about data, and show users meaningful calculations regarding hidden details inside datasets.
MEDIAN: obtains the middle value of the dataset if the dataset contains an odd number of values, or the interpolation between the two middle values if the dataset contains an even number of values. The syntax for using this function is MEDIAN(metric field). For example, MEDIAN({Q1.4 Number of clicks}).
MODE: obtains the value that occurs most frequently in the dataset. The syntax for using this function is MODE(metric field). For example, MODE({Q1.4 Number of clicks}).
STANDARD_DEVIATION: obtains a value that is the result of calculating the variation of individual data points in the dataset from the mean. Standard Deviation is a statistical function that measures the dispersion of a dataset. A high standard deviation indicates a large spread of data values, while a low value indicates a concentration of values. The syntax for using this function is STANDARD_DEVIATION(metric field). For example, STANDARD_DEVIATION({Q1.4 Number of clicks}).
PERCENTILE: obtains the kth percentile of values in a dataset. The percentile is a measure of position used in statistics that indicates, after ordering the field values from lowest to highest, the value in the field below which a given percentage of values in the dataset are located. For example, the 20th percentile is the value below which 20% of the values are located, and the remaining 80% are higher. The syntax for using this function is PERCENTILE(metric field, num), where “num” is an integer between 1 and 100. For example, PERCENTILE({Q1.4 Number of clicks}, 95).
QUARTILE: obtains the kth quartile of values in a dataset. A quartile is a statistical measure that divides an ordered dataset into four equal parts. Each quartile shows a specific value under which a certain percentage of the data falls. The first quartile (num = 1) corresponds to the 25th percentile and is the value under which 25% of the values fall. The second quartile (num = 2) corresponds to the 50th percentile, is the median, and divides the data into two equal halves, being a significant indicator of central tendency. The third quartile (num = 3) corresponds to the 75th percentile and is the value under which 75% of the values fall. Quartiles are useful in a variety of statistical applications to describe variability and extremes in data. The syntax for using this function is QUARTILE(metric field, num), where “num” is an integer between 1 and 3. For example, QUARTILE({Q1.4 Number of clicks}, 3).
TERCILE: obtains the k-th tercile of values in a data set. A tercile is a statistical measure that divides a sorted data set into three equal parts. Each tercile shows a specific value under which a certain percentage of the data falls. The first tercile (num = 1) is the value under which 33.33% of the values fall. The second tercile (num = 2) is the value under which 66.66% of the values fall. The syntax for using this function is TERCILE(metric field, num), where num is an integer between 1 and 2. For example, TERCILE({Q1.4 Number of clicks}, 2).
VARIANCE: obtains the variance corresponding to the dataset passed as a parameter. In descriptive statistics, the variance is the mean of the squares of the deviations from the mean. Functionally, it is a measure of dispersion of the values contained in the dataset. The syntax for using this function is VARIANCE(metric field). For example, VARIANCE({Q1.4 Number of clicks}).
CORRELATION: obtains the correlation coefficient between the two sets of data passed as parameters. Correlation is a statistical measure of how closely two variables are linearly related, meaning they change together at a constant rate. It is a common statistical tool for describing simple relationships without regard to cause and effect. The sample correlation coefficient ranges from -1 to +1 (unitless). The closer it is to zero, the weaker the linear relationship. Positive values indicate a positive correlation, where the values of both variables tend to increase together. Negative values indicate a negative correlation, where the values of one variable tend to increase while the values of the other variable decrease. The syntax for using this function is CORRELATION(metric field, metric field). For example, CORRELATION({Q1.4 Number of clicks}, {Q1.5 Duration of session}).
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})
CONTAINS: indicates whether the text string passed as the first parameter contains the text string passed as the second parameter, i.e. returns true or false (boolean field, as true or false). The syntax for using this function is CONTAINS(dimension field, string). For example, for the value ‘Spain’ CONTAINS({Q1.1 Country}, ‘ain’) would return true.
ENDS_WITH: Indicates whether the text string passed as the first parameter ends with the text string passed as the second parameter, that is, returns true or false (boolean field, as true or false). The syntax to use this function is ENDS_WITH(dimension_field, string). For example, for the value ‘Spain’ ENDS_WITH({Q1.1 Country}, ‘ain’) would return true.
STARTS_WITH: indicates whether the text string passed as the first parameter starts with the text string passed as the second parameter, that is, returns true or false (boolean field, such as true or false). The syntax for using this function is STARTS_WITH(dimension_field, string). For example, for the value ‘Spain’ STARTS_WITH({Q1.1 Country}, ‘ain’) would return false.
CAPITALIZE: obtains the text string passed as a parameter with the first character capitalized. The syntax for using this function is CAPITALIZE(dimension_field). For example, for the value ‘products’ CAPITALIZE({Q1.1 Product Name}) would return ‘Products’.
LOWER: obtains the entire text string passed as a parameter in lowercase. The syntax for using this function is LOWER(dimension_field). For example, for the value ‘Products’ LOWER({Q1.1 Product Name}) would return ‘products’.
UPPER: obtains the entire text string passed as a parameter in uppercase. The syntax for using this function is UPPER(dimension_field). For example, for the value ‘Products’ UPPER({Q1.1 Product Name}) would return ‘PRODUCTS’.
FIND: obtains the position where the text string passed as the second parameter is found within the text string passed as the first parameter. 0 is returned if it is not found, 1 if it is found in the first character, and so on. Optionally, a third parameter can be indicated with the position from which the search is performed. The syntax for using this function is FIND(dimension field, string, num). For example, for the value ‘Nebraska’ FIND({Q1.1 Region}, ‘a’) will return 5, FIND({Q1.1 Region}, ‘a’, 6) will return 8, and FIND({Q1.1 Region}, ‘o’, 2) will return 0.
FINDNTH: obtains the position where the num occurrence of the text string passed as the second parameter is found within the text string passed as the first parameter. 0 is returned if not found, 1 if found in the first character, and so on. The syntax for using this function is FINDNTH(dimension field, string, num). For example, for the value ‘Nebraska’ FINDNTH({Q1.1 Region}, ‘a’, 1) will return 5, FINDNTH({Q1.1 Region}, ‘a’, 3) will return 0.
LEN: obtains the length of the text string passed as a parameter. The syntax for using this function is LEN(dimension field). For example, for the value ‘Nebraska’ LEN({Q1.1 Region}) will return 8.
LEFT: obtains the first num characters from the text string passed as the first parameter, i.e. starting from the left. The syntax for using this function is LEFT(dimension field, num). For example, for the value ‘Nebraska’ LEFT({Q1.1 Region}, 3) will return ‘Neb’.
RIGHT: obtains the last num characters of the text string passed as the first parameter, i.e. starting from the right. The syntax for using this function is RIGHT(dimension field, num). For example, for the value ‘Nebraska’ RIGHT({Q1.1 Region}, 3) will return ‘ska’.
LTRIM: obtains the text string passed as the first parameter without any leading whitespace. The syntax to use this function is LTRIM(dimension field). For example, for the value ‘ Nebr aska ’ LTRIM({Q1.1 Region}) will return ‘Nebr aska ’.
RTRIM: obtains the text string passed as parameter without any trailing whitespace. The syntax to use this function is RTRIM(dimension field). For example, for the value ‘ Nebr aska ’ LTRIM({Q1.1 Region}) will return ‘ Nebr aska’.
TRIM: obtains the text string passed as parameter without any leading or trailing whitespace. The syntax to use this function is TRIM(dimension field). For example, for the value ‘ Nebr aska ’ TRIM({Q1.1 Region}) will return ‘Nebr aska’.
SUBSTRING: obtains the substring that results from the text string passed as the first parameter, starting from the (start) position and going through a number of characters (length). It is a very common function to obtain parts of text strings, even in conjunction with other functions to obtain the start positions (start) and the length of the resulting string (length), such as FIND and LEN. The syntax to use this function is SUBSTRING(dimension field, start, length). For example, for the value ‘Nebraska’ SUBSTRING({Q1.1 Region}, 1, 3) will return ‘Neb’.
REPLACE: obtains the text string that results from replacing the string old_string with the string new_string in the text string passed as the first parameter. The syntax for using this function is REPLACE(dimension field, old_string, new_string). For example, for the value ‘Nebraska’ REPLACE({Q1.1 Region}, ’aska’, ‘askas#’) will return ‘Nebraskas#’.
SPACE: obtains a string consisting of the specified number of repeated spaces. The syntax for using this function is SPACE(num). For example, SPACE(5) will return ‘ ’.
SPLIT: obtains a substring from the text string passed as the first parameter, using the text string passed in the second parameter as the delimiter and the third parameter is the position of the substring to return. The syntax to use this function is SPLIT(dimension field, string, num). For example, for the value ‘Nebraska;Washington;Miami’ SPLIT({Q1.1 Region}, ’;’, 3) will return ‘Miami’.
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')
