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 Add column button

2. Enter your desired column name and formula:

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}

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.