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


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