Adding formula-based custom columns
Adding calculable columns is especially helpful in cases where you need to have a certain column in your dataset that calculates a value based on existing data. It is also helpful if you have multiple sources that are of different formatting from each other. You could use calculable columns to create one universal column to make your data more unified.
1. To add a formula column, you must click on the Formula button
2. Enter your desired column name, add the formula and the needed format:
3. Press Add Column button. Added columns will now reflect on the table:
Unlike the normal columns fetched from the source, added columns have an additional option to delete the column (other columns can only be hidden). To delete an added column, you just need to hover over the desired column to delete, click the menu icon that will appear, and click the delete column button.
Notes:
- You cannot use an added calculable column name in the formula. Formulas are only applicable to the original column names fetched from your sources.
- If the value cannot be converted properly (could be due to incorrect column type, or adding an argument where not accepted), the method will return NaN (Not a Number)
Supported operations and formulas
| Operations / Formulas | Definition | Input Type | Example of usage |
| Arithmetic Operations | |||
| + | Plus sign (+), arithmetic operation - addition. | Number | {Column1}+{Column2} |
| - | Minus sign (-), arithmetic operation - subtraction. | Number | {Column1}-{Column2} |
| * | Multiplication sign (*), arithmetic operation multiplication. | Number | {Column1}*{Column2} |
| / | Divide sign (/), arithmetic operation - division. | Number | {Column1}/{Column2} |
| ^ | Caret symbol (^) is used to represent the exponentiation operator, which raises a number to a power. | Number | {Column1}^{Column2} |
| == |
Equality. | any | if({Column1}=={Column2}, "correct", "incorrect") {Column1}=={Column2} |
| != |
Inequality. | any | if({Column1}!={Column2}, "correct", "incorrect") {Column1}!={Column2} |
| < |
Less than. | any | if({Column1}<{Column2}, "correct", "incorrect") {Column1}<{Column2} |
| <= |
Less than or equal. | any | if({Column1}<={Column2}, "correct", "incorrect") {Column1}<={Column2} |
| > |
Greater than. | any | if({Column1}>{Column2}, "correct", "incorrect") {Column1}>{Column2} |
| >= |
Greater than or equal. | any | if({Column1}>={Column2}, "correct", "incorrect") {Column1}>={Column2} |
| abs({arg}) | Returns the absolute value of the number (e.g. if the column has a negative value (-100) it will return an absolute number (100), also it returns an absolute number if a string contains numbers. | Number, String | abs({Column4}) abs({Column5}(number_as_string)) |
| mod({arg},denominator) | Returns the result of the modulo operator, the remainder after a division operation. | Number | mod({Column1}, 2) |
| max({arg1},{arg2},...) | Returns the maximum value of a set of numerical values. | Number | max({Column1}, {Column2}) |
| min({arg1},{arg2},...) | Returns the minimum value in a numeric dataset. | Number | min({Column1}, {Column2}) |
| Strings Formulas | |||
| & | Ampersand (&) operator is used to combine two or more values to yield a single text value. | any | {Column1}&{Column2}&{Column3} |
| concat({arg1},{arg2},...) | Returns the concatenation of two values. Equivalent to the & operator, but limited to 42 arguments only. | any | concat({Column1},{Column2},{Column3}) |
| replace({arg},pattern,replacement) | Takes three arguments: value, pattern, and replacement. It returns a string where all occurrences of pattern in value have been replaced by replacement. | any | replace({Column9},{Column10},{Column11}) |
| substring({arg},indStart,indEnd) | Extracts characters, between two indices (positions), from a string, and returns the substring. | Number, String, Date, Date Time | substring({Column7},3,2) |
| find({arg},textToFind) | Returns the position at which a string is first found within the text. | Number, String, Date, Date Time | find({Column7}, "x") |
| length({arg}) | Returns the length of a string. | Number, String, Date, Date Time | length({Column7}) |
| trim({arg}) | Removes all spaces before and after the string (except for single spaces between words). Supports second argument - determines which character will be removed from the source string. It is an optional argument. |
Number, String, Date, Date Time | trim({Column7}) - will remove spaces trim({Column7},"_") - will remove underscore signs |
| upper({arg}) | Converts text to all capital letters (UPPERCASE). | String | upper({Column11}) |
| lower({arg}) | Converts text to all lowercase letters (lowercase). | String | lower({Column11}) |
| left({arg}, howMany) | Extract characters from the beginning of the string. | String | left("quick brown fox", 5) => quick |
| right({arg}, howMany) | Extract characters from the end of the string. | String | right("quick brown fox", 6) => wn fox |
| regex_match({arg}, {regex}) | Returns whether the input text matches a regular expression. | String | regex_match("Hello World", "Hello.World") => 1 |
| regex_extract({arg}, {regex}) | Returns the first substring that matches a regular expression. | String | regex_extract("Hello World", "W.*") => "World" |
| regex_replace({arg}, {regex}, {replacement}) | Substitutes all matching substrings with a replacement string value. | String | regex_replace("Hello World", " W.*", "") => "Hello" |
| Logical Operators | |||
| ! | A logical operator not (!) is a unary operator that takes a single Boolean value as its operand and returns the opposite Boolean value. | Boolean | !LogicalColumn |
| if({arg1},{ifTrue},{ifFalse}) | Returns one value if a logical expression is TRUE and another if it is FALSE. | any |
if({Column1}<={Column2}, "incorrect", "correct")
|
| and({arg1},{arg2},...) | Returns 1 if all the arguments are true, returns 0 otherwise. | any |
and({Column1}>0, {Column2}>0) -
returns 1 if both arguments are true or
returns 0 if any of the argument is false
|
| or({arg1},{arg2},...) | Returns 1 if any one of the arguments is true. | any |
OR({Column1}>0, {Column2}<0) -
returns
1 if one of the arguments is true or returns 0 if all of the arguments are false
|
| not({arg1},{arg2},...) | Reverses the logical value of its argument. | any |
NOT({Column1} = 0) - returns 1 if Column1 is not equal to zero or returns 0 if Column1 is equal to zero |
| coalesce({arg1},{arg2},...) | Returns the first non-NULL value from a series of arguments. | any | coalesce({Column1}, {Column2}, {Column3}) |
| ifnull({arg},value) | Specifies a value other than a null that is returned when a null is encountered. | any | ifnull({Column1}, "value_to_insert") |
| nullif({arg1},{arg2}) | Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned. | any | nullif({Column1}, {Column2}) |
| Financial Formulas | |||
| round({arg},{precision}) | Rounds a number to a specified number of digits. The second optional argument is the number indicating up to how many decimal places N will be rounded. | Number | round({Column5}) round({Column5}, 2) |
| ceil({arg}) | Rounds the given numerical value up to the nearest integer (the integer that is greater than or equal to the given value). | Number | ceil({Column6}) |
| floor({arg}) | Rounds a number down to the nearest integer multiple of the specified significance factor. | Number | floor({Column5}) |
| Math Formulas | |||
| log10({arg}) | Returns the base-10 logarithm of the given numerical value. | Number | log10({Column7}) |
| log2({arg}) | Returns the natural logarithm of a number to base-2. | Number | log2({Column1}) |
| ln({arg}) | Returns the natural logarithm of a given number. | Number | ln({Column6}) |
| exp({arg}) | Returns the value of the mathematical constant 'e' (also known as Euler's number) raised to the power of a given number. | Number | exp({Column1}) |
| sqrt({arg}) | Returns the square root of that argument. | Number | sqrt({Column6}) |
| radians({arg}) | Converts an angle value in degrees to radians. | Number | radians({Column1}) |
| degrees({arg}) | Converts an angle in radians to degrees. | Number | degrees({Column7}) |
| sin({arg}) | Returns the sine of an angle provided in radians. | Number | sin({Column1}) |
| cos({arg}) | Returns the cosine of an angle provided in radians. | Number | cos({Column7}) |
| tan({arg}) | Returns the tangent of an angle provided in radians. | Number | tan({Column1}) |
| trunc({arg}) | Returns the integer part of a number by removing any fractional digits. | Number | trunc({Column6}) |
| sign({arg}) | Returns the sign of that argument as an integer. If the argument is positive, the function returns 1. If the argument is negative, the function returns -1. | Number | sign({Column6}) |
| pi() | Returns the number pi. | -- |
pi()*{Column1} |
| rand() | Returns a random number between 0 inclusive and 1 exclusive. | -- | rand() |
| Date Formulas | |||
| now() | Returns the current date and time. Note: updates each time when user makes any actions with the columns: e.g. edit, resize, etc. |
-- | now() |
| today() | Returns the current date. |
-- | today() |
| second({arg}) | Returns the second of a datetime as an integer between 0 and 59. |
Date Time in ISO 8601 format |
second("2023-05-20T10:17:09.753Z") - returns “9” |
| minute({arg}) | Returns the minute of a datetime as an integer between 0 and 59. | Date Time in ISO 8601 format |
minute("2023-05-20T10:17:09.753Z") - returns “17” |
| hour({arg}) | Returns the hour of a datetime as a number between 0 (12:00am) and 23 (11:00pm). | Date Time in ISO 8601 format |
hour("2023-05-31T23:17:35.767Z") - returns “23” |
| day({arg}) | Returns the day of the month of a datetime in the form of a number between 1-31. |
Date Time in ISO 8601 format |
day("2023-05-20T10:17:09.753Z") - returns “20” |
| month({arg}) | Returns the month of a datetime as a number between 1 (January) and 12 (December). |
Date Time in ISO 8601 format |
month("2023-05-20T10:17:09.753Z") - returns “5” |
| year({arg}) | Returns the four-digit year of a datetime. |
Date Time ISO 8601 format |
year("2021-06-09") - returns “2021” |
| weekday({arg}) | Returns the day of the week as an integer between 0 (Sunday) and 6 (Saturday), inclusive. |
Date Time in ISO 8601 format |
weekday("2023-05-31T10:59:45.457Z") - returns “3” |
| iso_weekday({arg}) | Returns the day of the week as an integer between 1 (Monday) to 7 (Sunday), inclusive. |
Date Time in ISO 8601 format |
iso_weekday("2023-05-31") - returns “3” |
| weeknum({arg}) | Returns the week number in a year. |
Date Time in ISO 8601 format |
weeknum("02/17/2013") - returns “7” |
| iso_weeknum({arg}) | Returns the ISO week number in a year. |
Date Time in ISO 8601 format |
iso_weeknum("02/17/2013") - returns “7” |
| datetime_format({arg},'specified output format') | Formats a datetime into a specified string. | Date Time in ISO 8601 format |
datetime_format("2023-05-20T10:17:09.753Z", 'YYYY-MM-DD HH:MM:ss') - returns “2023-05-20 10:17:09” datetime_format(now(), 'YYYY') - returns “2023” |
| datetime_add({arg},#,'units') | Adds or substracts specified 'count' units to a datetime. | Date Time in ISO 8601 format |
datetime_add("2023-05-20T10:17:09.753Z", 5, "days") - returns "2023-05-25T10:17:09.753Z" datetime_add(now(), -7, "hours") - returns current date and time in UTC -7 hours |
| datetime_diff({arg1},{arg2},'units') | Returns the difference between datetimes in specified units. | Date Time in ISO 8601 format |
datetime_diff("2023-05-25","2023-05-20", "days") - returns "5" datetime_diff("2023-05-20T10:17:13.753Z","2023-05-20T10:17:09.753Z","seconds") - returns "4" |
| datetime_parse({arg},'input format') | Interprets a text string as a structured date and time, with optional input format and locale parameters. | String |
datetime_parse("4 Mar 2017 23:00", 'D MMM YYYY HH:mm') - returns "2017-03-04T23:00:00.000Z" |
| date_parse({arg},'input format') | Interprets a text string as a structured date, with optional input format and locale parameters. | String |
date_parse("4 Mar 2017", 'D MMM YYYY') - returns "2017-03-04" |
| is_after({arg1},{arg2}) | Determines if date1 is later than date2. Returns 1 if yes, 0 if no. | Date Time ISO 8601 |
is_after("1/1/1979", "1/1/2000") - returns "0" |
| is_before({arg1},{arg2}) | Determines if date1 is earlier than date2. Returns 1 if yes, 0 if no. | Date Time ISO 8601 |
is_before("1/1/1979", "1/1/2000") - returns "1" |
| is_same({arg1},{arg2}) | Compares two dates up to a unit and determines whether they are identical. Returns 1 if yes, 0 if no. | Date Time ISO 8601 |
is_same("1/1/1979", "1/1/1979") - returns "1" |
| start_of_time({arg},unit) | Returns start of date period according to the unit value | Date Time ISO 8601 |
start_of_time("2023-07-18", "month") - returns "2023-07-01T00:00:00" |
| end_of_time({arg},unit) | Returns end of date period according to the unit value | Date Time ISO 8601 |
end_of_time("2023-07-18", "day") - returns "2023-07-18T23:59:59.999" |
| Data types | |||
| number() | A function that casts the value to a number. | any | number({Column4}) |
| string() | A function that casts the value to a string. | any | string({Column1}) |
| null() | Returns `null`. | -- | null() |
| Help Signs | |||
| ( ) | Used to group terms together or specify the order of operations in a complex calculation. | |
({Column1}+{Column2})/{Column2} |
| {} | Used to define the column name in the formula. | |
{Column 1}+{Column2} |