Formulas

What does it do?

Creates a new column with the ouput of the formula.

How to use it?

How to configure it?

You can type any formula in the Formula text area. Available area are shown below.

Available Formulas

IF(condition; expression_if_true; expression_if_false)

Takes three arguments, the first one must be a boolean and the second and third must return the same type.

In the expression_if_true and expression_if_false part, you can use comparators and mathematical operations such as: + ,- ,/ ,* ,> ,< ,= ,>= ,<= ,like

Examples:

IF(column_a > column_b; "column_a is bigger than column_b"; "column_b is bigger than column_a")
IF(column_a + column_b <= 10; "Bigger than 10"; "less than 10")
IF(column_a like "%toto%"; "Toto detected"; "Toto is not here")

Like operator

The like operator is used to compare strings together and can be used to do "begins with", "ends with", "contains".

Begins with "toto": column_a like "toto%"

Ends with "toto": column_a like "%toto"

Contains "toto": column_a like "%toto%"

URLs operators

UTM_SOURCE(expression)

Takes an url as an input and return the utm source url parameter if exists or null.

UTM_SOURCE("https://whaly.io?utm_source=linkedin") => "linkedin"

UTM_CAMPAIGN(expression)

Takes an url as an input and return the utm campaign url parameter if exists or null.

UTM_CAMPAIGN("https://whaly.io?utm_campaign=yc-announcement") => "yc-announcement"

UTM_MEDIUM(expression)

Takes an url as an input and return the utm medium url parameter if exists or null.

UTM_MEDIUM("https://whaly.io?utm_medium=social") => "social"

UTM_TERM(expression)

Takes an url as an input and return the utm term url parameter if exists or null.

UTM_TERM("https://whaly.io?utm_term=startup") => "startup"

UTM_CONTENT(expression)

Takes an url as an input and return the utm content url parameter if exists or null.

UTM_CONTENT("https://whaly.io?utm_content=flavor_a") => "flavor_a"

DOMAIN(expression)

Takes an url as an input and return the domain of the url parameter if exists or null

DOMAIN("https://whaly.io?utm_content=flavor_a") => "whaly.io"

IS_NULL(expression)

Takes a column as an input and returns a boolean. Return true if null or false if not null.

IS_NULL(null) => true
IS_NULL("ycombinator") => false
IS_NULL(0) => false

IS_NOT_NULL(expression)

Takes a column as an input and returns a boolean. Return true if not null or false if null.

IS_NOT_NULL(null) => false
IS_NOT_NULL("ycombinator") => true
IS_NOT_NULL(0) => true

URL(url, label)

Takes two column as an input and returns an html element that can be clicked on

URL("https://google.com"; "click me !")

Date operators

NOW

Return the current time.

NOW => "2022-09-02"

DATEVALUE(expression)

Takes a number representing the number of milliseconds since the 1st January 1970 as an input and returns the associated date.

DATEVALUE(1630598507000) => "2021-09-02"

DATEDIF(date1; date2; resultDateType)

Returns the duration between date1 and date2 (date1 - date2), in Day, Month or Week.

DATEDIF("2021-02-01"; "2021-01-01"; "MONTH") => 1
DATEDIF("2021-02-01"; "2021-01-01"; "DAY") => 30

resultDateType can be: MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH.

PARSE_GOOGLESHEET_TIMESTAMP(timestampColumn)

Convert a Google Sheet timestamp (number of days since the 31th december 1899) into a proper date.

PARSE_GOOGLESHEET_TIMESTAMP(1) => "1899-12-31"

COHORT(expression, Type)

Returns a cohort when inputed a timestamp. Part can be: DAY, WEEK ,MONTH or YEAR.

COHORT("2018-12-19T12:00:00"; "DAY") => "2018-12-19T00:00:00"
COHORT("2018-12-19T12:00:00"; "WEEK") => "2018-12-17T00:00:00"
COHORT("2018-12-19T12:00:00"; "MONTH") => "2018-12-01T00:00:00"
COHORT("2018-12-19T12:00:00"; "YEAR") => "2018-01-01T00:00:00"

COHORT_WITH_TZ(expression, Type, Timezone)

Returns a cohort when inputed a date. Part can be: DAY, WEEK ,MONTH or YEAR.

Timezone will be used to calculate the proper DAY, WEEK, etc. in the Timezone. It should follow the Timezone Database Name format, ex Europe/Paris

COHORT_WITH_TZ("2018-12-19T22:00:00"; "DAY"; "Europe/Paris") => "2018-12-20T00:00:00"
COHORT_WITH_TZ("2018-12-19T22:00:00"; "WEEK") => "2018-12-20T00:00:00"
COHORT_WITH_TZ("2018-12-19T22:00:00"; "MONTH") => "2018-12-01T00:00:00"
COHORT_WITH_TZ("2018-12-19T22:00:00"; "YEAR") => "2018-01-01T00:00:00"

DATEADD(Date; Interval; Part)

Add period to a date. Part can be: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

DATE_ADD("2018-12-19"; 1; "DAY") => "2018-12-20"
DATE_ADD("2018-12-19"; 1; "YEAR") => "2019-12-19"

DATETIME_FORMAT(expression; format)

Format a date into string. Supported format are described below.

DATETIME_FORMAT("2018-12-19"; "%A, %B %e, %Y") => "Wednesday, December 19, 2018"
DATETIME_FORMAT("2018-12-19"; "%B") => "December"

DATETIME_PARSE(expression; format)

Parse a string that match the format and turn it into a Date. Supported format are described below.

DATETIME_PARSE("Wednesday, December 19, 2018"; "%A, %B %e, %Y") => "2018-12-19"

Text operators

CONCAT(...expressions)

Takes several columns separated by ";" and returns a concatenated string. Ex:

CONCAT("Hello"; " "; "World") => "Hello World"

LEFT(string; howMany)

Extract howMany characters from the beginning of the string.

LEFT("quick brown fox"; 5) => "quick"

RIGHT(string; howMany)

Extract howMany characters from the end of the string.

RIGHT("quick brown fox"; 3) => "fox"

MID(string; whereToStart; count)

Extract a substring of count characters starting at whereToStart.

MID("quick brown fox"; 7; 5) => "brown"

LENGTH(string)

Count the number of characters in the String.

LENGTH("whaly") => 5

SPLIT(columnToBeSplitted; splitSeparator; indexToGet)

Split a column content according to a separator and then return the value at the given index in the splitted array.

SPLIT("CatA > CatB > CatC"; ">"; 1) => "CatB"

Logical operators

AND(expressionA; expressionB)

Return true if expressionA and expressionB are true.

AND(true; true) => true
AND(true; false) => false
AND(false; false) => false

OR(expressionA; expressionB)

Return true if expressionA or expressionB are true.

OR(true; true) => true
OR(true; false) => true
OR(false; false) => false

Number operators

ROUND(expression; precision)

Round a number with a precision.

ROUND(3.14; 0) => 3
ROUND(3.14; 1) => 3.1
ROUND(4.65; 1) => 4.7

FLOOR(expression)

Floor a number, get rid of the decimals.

FLOOR(3.14) => 3
FLOOR(3.1) => 3
FLOOR(3) => 3

VALUE(expression)

Convert a text into an integer number.

VALUE("3") => 3
VALUE("3.2") => 3
VALUE("Invalid") => null

DATETIME formula format

Unless otherwise noted, DATETIME functions that use format strings support the following elements:

When using BigQuery warehouse

When using Snowflake Warehouse

Last updated