Data Management

Join

How to Create Calculated Columns in MX Deposit

MarissaDoherty
MarissaDoherty Posts: 30 admin
edited November 2023 in Social Groups

When configuring your tables, you can choose whether a column is a calculated column or user input column. You can then select the formula to edit what is already there or begin to build out the formula you wish to use for calculating the fields in that column.  

To add a numeric column, click on the numeric icon and activate the 'Calculated' option. This will then bring a message "Click here to open the formula editor" at the bottom of the window:

img1 (3).png

The formula editor offers drag-and-drop functionality for ease of use, however if you prefer to write your formulas from scratch, that method of formula expression can also be used. The scope of what you can calculate is determined by what columns you have in your table – e.g. RQD Length, Recovery Length, RQD %, etc. - and the list of functions available, which includes a range of options from basic operators to string, numeric, conditional, aggregate, and trigonometric functions (see below for a full list). 

If the formula that you have created won’t function and the system can’t evaluate the expression, the built-in validation will alert you so that you can fix typos or make any corrections before proceeding. The system is constantly reviewing and validating formulas from a syntax and data type perspective to ensure that the formulas you create are valid and will provide you with accurate outputs. 

The ‘Edit Input Columns’ button lets you see a list of the columns being used in your formulas and manage whether a column value is ‘required’, meaning all columns must have values before the formula runs. If not, add a default value so the calculation runs.  

Resolution:

You can create calculated columns to derive new values by using any combination of:

  • Operators;
  • Functions;
  • Constants;
  • Table Columns (that contain data);
  • Other Calculated Columns.

When you add a calculated column to a table, it will become available to all activities that use the table. 


Operators

You can use any of the following operators in your formula expressions. MX Deposit follows the standard order of operations: brackets, exponents, multiplication, division, addition, and subtraction (BEDMAS).

  • Addition (+)
  • Subtraction (-)
  • Division (/)
  • Multiplication (*)
  • Modulo (%)
  • Less than (<)
  • Greater than (>)
  • Less than or equal to (<=)
  • Greater than or equal to (>=)
  • Equal (==)
  • Not equal (!=)
  • AND (&)
  • OR (I)

 Functions

You can use any of the following functions in your formula expressions. Functions are organized into the following categories:

  • String Functions
  • Numeric Functions
  • Row Aggregate Functions
  • Conditional Functions
  • Trigonometric Functions

i) String Functions

You can include the following string (text) functions in your formula expressions:

  • str - formats the input expression as a string.
    • str(expression)
  • left - returns the specified number of the leftmost characters from a string.
    • left(expression, limit)
  • right - returns the specified number of the rightmost characters from a string.
    • right(expression, limit)
  • upper - formats a string in all uppercase.
    • upper(expression)
  • lower - formats a string in all lowercase.
    • lower(expression)
  • trim - removes both preceding and following blank space from a string. Optionally specify the specific text to be removed. 
    • trim(expression, text)
  • rtrim - removes following blank spaces from a string. Optionally specify the specific text to be removed. 
    • rtrim(expression, text)
  • ltrim - removes preceding blank spaces from a string. Optionally specify the specific text to be removed. 
    • ltrim(expression, text)
  • len - returns the number of characters in a string. 
    • len(expression)
  • concat - concatenates two or more strings separated by delimiter character. 
    • concat(expression1, expression2 [,expression3 ...], delimiter)

ii) Numeric Functions

You can include the following numeric (mathematical) functions in your formula expressions:

  • trunc - removes all digits to the right of the decimal point for any value: 
    • trunc(decimal)
  • round - rounds a decimal value to the closest integer: 
    • round(decimal)
  • ceil - rounds a decimal value to the next highest integer: 
    • ceil(decimal)
  • floor - decrements a decimal value to the next lowest integer: 
    • floor(decimal)
  • abs - returns the absolute value of a given expression: 
    • abs(expression)
  • pow - returns the given expression raised to the specified power: 
    • pow(expression, power)
  • log - returns the natural logarithm of expression to base e or to optionally specified base: 
    • log(expression, base) 

iii) Row Aggregate Functions

Row aggregate functions produce a single output value across a set of input values. You can include the following row aggregate functions in your formula expressions:

  • avg - averages the set of specified values: 
    • avg(numeric1, numeric2 [, numeric3 ...])
  • median - returns the median value of the specified values: 
    • median(numeric1, numeric2 [, numeric3 ...])
  • min - returns the minimum value of the specified values: 
    • min(numeric1, numeric2 [, numeric3 ...])
  • max - returns the maximum value of the specified values: 
    • max(numeric1, numeric2 [, numeric3 ...])

iv) Conditional Functions

You can include the following conditional functions in your formula expressions:

  • ifthen - evaluates a set of [if, then] expression pairings, and returns the values of the [then] argument for the first [if] argument that evaluates to true:
    • ifthen([if-expression1, then-expression1],[if-expression2, then-expression2],...)
  • ifelse - evaluates a set of [if, then, else] expression pairings, and returns the values of the [then] argument for the first [if] argument that evaluates to true. If all [if] arguments evaluates to false, then the value of the [else] argument is returned: 
    • ifelse(if-expression, then-expression, else-expression)

v) Trigonometric Functions

You can include the following trigonometric functions in your formula expressions:

  • sin - calculates the trigonometric sine of an angle, in radians: 
    • sin(angle)
  • cos - calculates the trigonometric cosine of an angle, in radians: 
    • cos(angle)
  • tan - calculates the trigonometric tangent of an angle, in radians: 
    • tan(angle)
  • asin - calculates the arc sine of an angle, in radians: 
    • asin(angle)
  • acos - calculates the arc cosine of an angle, in radians: 
    • acos(angle)
  • atan - calculates the arc tangent of an angle, in radians: 
    • atan(angle)
  • atan2 - calculates atan(y/x), in radians. The vector in the plane from the origin to point (x,y) makes this angle with the positive X axis. The point of atan2() is that the signs of both inputs are known to it, so it can compute the correct quadrant for the angle: 
    • atan2(y, x)

Constants

You can include the following constants in your formula expressions:

  • E - Euler's number, a mathematical constant approximately equal to 2.71828. It is the base of the natural logarithms.
  • PI - mathematical constant that is the ratio of a circle's circumference to its diameter, approximately equal to 3.14159.


Notes:

For a quick demonstration of this new functionality, please see the video at https://my.seequent.com/products/mx-deposit/latest  

Tagged:

Categories