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 of different formatting from each other. You could use calculable columns to create one universal column to make your data more unified.
Please note that you cannot "stack" formulas (i.e. Creating a formula using another formula as a parameter)
How to write a formula
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:
How to delete a formula
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 must:
1. Hover over the desired column to delete
2. Click the menu icon that will appear
3. Click the delete column button:
Output Formats
The formulas would return either a number, date, or text results. In the list below, we will describe which formats are available per each type:
Number Formats
Integer
Decimal - can specify precision
Percent - can specify precision
Currency - can specify precision and currency (Note: selecting a currency will not actually convert the value. It will just add the currency symbol before the value.)
Date Formats
No formatting option for date formats. It will always return Date or Date Time in ISO 8601 format
Text Formats
No formatting option for text formats.
Formula Terminology
Element
Definition
Example
{}
Used to define the column name in the formula. If you are copy-pasting a formula, you must always include the brackets. But, if you are typing your formula from scratch, you can opt to skip adding curly braces as they will be added automatically when you select the field name on the suggestions. A suggestion with the below symbol will be suggested as you type. This symbol indicates that it's a field/column:
{Column1}
()
Used to group terms together or specify the order of operations in a complex calculation.
({Column1} + {Column2}) / {Column3}
Operators
Symbols that are used to perform operations to produce a result
&
Function
These are the predefined operations that take input values, perform specific tasks, and produce output values. When typing a function on the formula box, a suggestion with the below symbol will be suggested as you type. This symbol indicates that it's a function:
length({Column7})
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 Functions
Numerical Operators
In the below examples, assume the following values of the used columns:
Column Name
Value
Type
Column1
10
Number
Column2
12
Number
Operators
Definition
Input Type
Example of Usage
Output Example
+
Performs the addition of two or more numbers
Number
{Column1}+{Colum2}
{Column1}+3
22
13
-
Performs the subtraction of two or more numbers
Number
{Column1}-{Column2}
{Column1}-10
-2
0
*
Performs the multiplication of two or more numbers
Number
{Column1}*{Column2}
{Column1}*0.10
120
1
/
Performs the division of two or more numbers
Number
{Column1}/{Column2}
{Column1}/5
0.83
2
^
Used to represent the exponentiation operator, which raises a number to a power.
Number
{Column1}^{Column2}
{Column1}^2
1,000,000,000,000
100
Numerical Functions
In the below examples, assume the following values of the used columns:
Column Name
Value
Type
Column1
100
Number
Column2
-200
Number
Column3
-300
String (number as a string)
Column4
321.1234567
Number
Functions
Definition
Input Type
Example of Usage
Output Example
mod({arg},denominator)
Returns the result of the modulo operator, the remainder after a division operation.
Number
mod({Column1}, 3)
1
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({Column2})
abs({Column3})
200
300
max({arg1},{arg2},...)
Returns the maximum value of a set of numerical values.
Number
max({Column1}, {Column2})
max({Column2}, {Column3})
100
-200
min({arg1},{arg2},...)
Returns the minimum value in a numeric dataset.
Number,
String
min({Column1}, {Column2})
min({Column2}, {Column3})
-200
-300
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({Column4})
round({Column4}, 4)
321
321.1235
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({Column4})
ceil({Column2})
322
-200
floor({arg})
Rounds a number down to the nearest integer multiple of the specified significance factor.
Number
floor({Column4})
floor({Column2})
321
-200
log10({arg})
Returns the base-10 logarithm of the given numerical value.
Number
log10({Column1})
log10({Column4})
2
2.506672030086284
log2({arg})
Returns the natural logarithm of a number to base-2.
Number
log2({Column1})
log2({Column4})
6.643856189774724
8.326984241411967
ln({arg})
Returns the natural logarithm of a given number.
Number
ln({Column1})
ln({Column4})
4.605170185988092
5.7718256495018005
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})
exp({Column4})
2.6881171418161356e+43
2.8983127978145803e+139
sqrt({arg})
Returns the square root of that argument.
Number
sqrt({Column1})
sqrt({Column4})
10
17.91991787648593
radians({arg})
Converts an angle value in degrees to radians.
Number
radians({Column1})
radians({Column2})
1.7453292519943295
-3.490658503988659
degrees({arg})
Converts an angle in radians to degrees.
Number
degrees({Column1})
degrees({Column2})
5729.5779513082325
-11459.155902616465
sin({arg})
Returns the sine of an angle provided in radians.
Number
sin({Column1})
sin({Column2})
-0.5063656411097588
0.8732972972139946
cos({arg})
Returns the cosine of an angle provided in radians.
Number
cos({Column1})
cos({Column2})
0.8623188722876839
0.4871876750070059
tan({arg})
Returns the tangent of an angle provided in radians.
Number
tan({Column1})
tan({Column2})
-0.5872139151569291
1.7925274837903817
trunc({arg})
Returns the integer part of a number by removing any fractional digits.
Number
trunc({Column1})
trunc({Column4})
100
321
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({Column1})
sign({Column3})
1
-1
pi()
Returns the number pi.
--
pi()*{Column1}
314.1592653589793
rand()
Returns a random number between 0 inclusive and 1 exclusive.
--
rand()
rand()
0.6958763053113555
0.9512031618840773
Logical Operators
In the below examples, assume the following values of the used columns:
Column Name
Value
Type
Column1
100
Number
Column2
100
Number
Column3
test
String
Column4
test
String
Column5
200
Number
Operators
Definition
Input Type
Example of Usage
Output Example
==
Equality. Returns 1 if true and 0 if false.
any
if({Column1}=={Column2}, "correct", "incorrect")
{Column3}=={Column4}
correct
1
!=
Inequality. Returns 1 if true and 0 if false.
any
if({Column1}!={Column2}, "correct", "incorrect")
{Column3}!={Column4}
incorrect
0
<
Less than. Returns 1 if true and 0 if false.
any
if({Column1}<{Column2}, "correct", "incorrect")
{Column1}<{Column5}
incorrect
1
<=
Less than or equal. Returns 1 if true and 0 if false.
any
if({Column1}<={Column2}, "correct", "incorrect")
{Column1}<={Column5}
correct
1
>
Greater than. Returns 1 if true and 0 if false.
any
if({Column1}>{Column2}, "correct", "incorrect")
{Column1}>{Column5}
incorrect
0
>=
Greater than or equal. Returns 1 if true and 0 if false.
any
if({Column1}>={Column2}, "correct", "incorrect")
{Column1}>={Column5}
correct
0
!
A logical operator not (!) is a unary operator that takes a single Boolean value as its operand and returns the opposite Boolean value. Returns 1 if true and 0 if false.
Boolean
!{{Column1}>{Column5}}
!({Column1}<{Column5})
1
0
Logical Functions
In the below examples, assume the following values of the used columns:
Column Name
Value
Type
Column1
100
Number
Column2
100
Number
Column3
test
String
Column4
test
String
Column5
NULL
-
Functions
Definition
Input Type
Example of Usage
Output Example
if({arg1},{ifTrue},{ifFalse})
Evaluates the expression. If the condition is TRUE, it produces a specified result, otherwise produces the evaluation for an else result.
any
if({Column1}<={Column2}, "incorrect", "correct")
if({Column3}=={Column4}, "correct", "incorrect")
correct
correct
and({arg1},{arg2},...)
Logical AND. Returns 1 if all the arguments are true, returns 0 otherwise.
any
and({Column1}>0, {Column2}>0)
and({Column1}=={Column2}, {Column3}=={Column4})
0
1
or({arg1},{arg2},...)
Logical OR. Returns 1 if any one of the arguments is true.
any
or({Column1}>0, {Column2}>0)
or({Column1}=={Column2}, {Column3}=={Column4})
1
1
not({arg})
Logical NOT. Reverses the logical value of its argument.
any
not({Column1} == {Column2})
not(and({Column1}>0, {Column2}>0))
0
1
coalesce({arg1},{arg2},...)
Returns the first non-NULL value from a series of arguments.
any
coalesce(null(), {Column5}, {Column4})
coalesce(null(), null(), {Column1})
test
100
ifnull({arg},value)
Specifies a value other than a null that is returned when a null is encountered.
any
ifnull({Column5}, "new_value")
ifnull({Column5}, {Column1})
new_value
100
nullif({arg1},{arg2})
Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned.
any
nullif({Column1}, {Column2})
nullif({Column3}, {Column2})
NULL
test
String Operators and Functions
In the below examples, assume the following values of the used columns:
Column Name
Value
Type
Column1
Product_A
String
Column2
Warehouse_1
String
Column3
_Product_A_
String
Column4
Hello World
String
Column5
{"key1":"value1", "key2":value2, "key3":value3}
String
Column6
{"key1": {"key2": {"key3": "value"}}}
String
Operators/Functions
Definition
Input Type
Example of Usage
Output Example
&
Ampersand (&) operator is used to combine two or more values to yield a single text value.
any
"[test] "&{Column1}
{Column2}&-&{Column1}
[test] Product_A
Warehouse_1-Product_A
concat({arg1},{arg2},...)
Returns the concatenation of two values. Equivalent to the & operator, but limited to 42 arguments only.
any
concat("[test] ",{Column1})
concat("{Column2}","-",{Column1})
[test] Product_A
Warehouse_1-Product_A
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({Column1},"_","+")
replace({Column2},"_"," ")
Product+A
Warehouse 1
substring({arg},indStart,charlength)
Extracts characters, between two indices (positions), from a string, and returns the substring. indStart refers to the "start at position" and "charlength" refers to how many characters are to be extracted.
Number, String, Date, Date Time
substring({Column1},length({Column1}),1)
substring({Column2},length({Column2}),1)
A
1
find({arg},textToFind)
Returns the position at which a string is first found within the text.
Number, String, Date, Date Time
find({Column1}, "_")
find({Column2}, "_")
8
10
length({arg})
Returns the length of a string.
Number, String, Date, Date Time
length({Column1})
length({Column2})
9
11
trim({arg}, textToRemove)
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({Column3},"_")
trim({Column2})
Product_A
Warehouse_1
upper({arg})
Converts text to all capital letters (UPPERCASE).
String
upper({Column1})
upper({Column2})
PRODUCT_A
WAREHOUSE_1
lower({arg})
Converts text to all lowercase letters (lowercase).
String
lower({Column1})
lower({Column2})
product_a
warehouse_1
left({arg}, howMany)
Extract characters from the beginning of the string.
String
left({Column1}, 4)
left({Column2}, 4)
Prod
Ware
right({arg}, howMany)
Extract characters from the end of the string.
String
right({Column1}, 6)
right({Column2}, 6)
duct_A
ouse_1
regex_match({arg}, {regex})
Returns whether the input text matches a regular expression. Returns 1 if true, 0 if false.
String
regex_match({Column4}, "Hello.World")
regex_match({Column1}, "Hello.World")
1
0
regex_extract({arg}, {regex})
Returns the first substring that matches a regular expression.
String
regex_extract({Column4},"W.*")
regex_extract({Column2},"W.*")
World
Warehouse_1
regex_replace({arg}, {regex}, {replacement})
Substitutes all matching substrings with a replacement string value.
String
regex_replace({Column4}, " W.*", "")
regex_replace({Column2}, " W.*", "")
Hello test
test
json_extract({arg}, json_path)
Extracts the value of the specified JSON key
String
json_extract({Column5}, "key1")
json_extract({Column6}, "key1.key2.key3")
value1
value
json_valid({arg})
Identifies if the given argument is a valid JSON. It returns 1 if the JSON in the specified column is valid and 0 if false.
String
json_valid({Column1})
json_valid({Column5})
0
1
Date and Time Functions
In the below examples, assume the following values of the used columns:
Column Name
Value
Type
Column1
2024-06-24T11:18:08.244Z
DateTime
Column2
2023-05-20T10:17:09.753Z
DateTime
Functions
Definition
Input Type
Example of Usage
Output Example
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()
2024-06-24T11:18:08.244Z
today()
Returns the current date.
--
today()
2024-06-24
second({arg})
Returns the second of a datetime as an integer between 0 and 59.
Date Time in ISO 8601 format
second({Column1}) second("2023-05-20T10:17:09.753Z")
8
9
minute({arg})
Returns the minute of a datetime as an integer between 0 and 59.
Date Time in ISO 8601 format
minute({Column1})
minute("2023-05-20T10:17:09.753Z")
18
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({Column1})
hour("2023-05-31T23:17:35.767Z")
11
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({Column1})
day("2023-05-20T10:17:09.753Z")
24
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({Column1})
month("2023-05-20T10:17:09.753Z")
6
5
year({arg})
Returns the four-digit year of a datetime.
Date Time ISO 8601 format
year({Column1})
year("2021-06-09")
2024
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({Column1})
weekday("2023-05-31T10:59:45.457Z")
1
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({Column1})
iso_weekday("2023-05-31")
1
3
weeknum({arg})
Returns the week number in a year.
Date Time in ISO 8601 format
weeknum({Column1})
weeknum("02/17/2013")
26
7
iso_weeknum({arg})
Returns the ISO week number in a year.
Date Time in ISO 8601 format
iso_weeknum({Column1})
iso_weeknum("02/17/2013")
26
7
datetime_format
({arg},'specified output format')
See all supported output formats in the dedicated article.
Formats a datetime into a specified string.
Date Time in ISO 8601 format
datetime_format({Column1}, 'YYYY-MM-DD HH:MM:ss')
datetime_format({Column1}, 'YYYY')
See all supported output formats in the dedicated article.
2024-06-24 11:06:08
2024
datetime_add({arg},#,'units')
Adds or substracts specified 'count' units to a datetime.
Date Time in ISO 8601 format
datetime_add({Column1}, 5, "days")
datetime_add({Column1}, -7, "hours")
2024-06-29T11:18:08.244Z2024-06-24T04:18:08.244Z
datetime_diff({arg1},{arg2},'units')
Returns the difference between datetimes in specified units.
Date Time in ISO 8601 format
datetime_diff({Column1}, {Column2}, "months")
datetime_diff("2023-05-20T10:17:13.753Z","2023-05-20T10:17:09.753Z","seconds")
13
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')
datetime_parse("24 Jun 2024 11:18", 'D MMM YYYY HH:mm')
2017-03-04T23:00:00.000Z
2024-06-24T11:18: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')
date_parse("24 Jun 2024", 'D MMM YYYY')
2017-03-04
2024-06-24
is_after({arg1},{arg2})
Determines if date1 is later than date2. Returns 1 if yes, 0 if no.
Date Time ISO 8601
is_after({Column1}, {Coulmn2})
is_after("1/1/1979", "1/1/2000")
1
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({Column1}, {Coulmn2})
is_before("1/1/1979", "1/1/2000")
0
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({Column1}, {Coulmn2})
is_same("1/1/1979", "1/1/1979")
0
1
start_of_time({arg},unit)
Returns start of date period according to the unit value
Date Time ISO 8601
start_of_time({Column1}, "month")
start_of_time("2023-07-18", "month")
2024-06-01T00:00:00.000Z
2023-07-01T00:00:00.000Z
end_of_time({arg},unit)
Returns end of date period according to the unit value
Date Time ISO 8601
end_of_time({Column1}, "month")
end_of_time("2023-07-18", "day")
2024-06-30T23:59:59.999Z
2023-07-18T23:59:59.999Z
Data Type Functions
In the below examples, assume the following values of the used columns:
Column Name
Value
Type
Column1
12345.67
String (number as a string)
Column2
4567
Number
Functions
Definition
Input Type
Example of Usage
Output Example
number()
A function that casts the value to a number.
any
number({Column1})
12345.67
string()
A function that casts the value to a string.
any
string({Column2})
4567
null()
Returns `null`.
--
null()
Example Use Cases
1. Addition. You are selling multiple products and wanted to see how much your Total Sales are.
Formula:
Output:
2. Subtraction. You wanted to do a financial analysis on your sales:
Formula:
Output:
3. Date Time Formatting. You wanted to save your date time values to BigQuery but your date time format is not supported by BigQuery (e.g. 1/28/2024 12:01:31)
Formula:
Output:
Last updated
Was this helpful?