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.

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.244Z

2024-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:

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