LogoLogo
HomeUser GuidesAPI Reference
  • ๐Ÿ‘Welcome to Whaly ๐Ÿณ
  • Team
    • ๐Ÿ‘จโ€๐Ÿ‘ฉโ€๐Ÿ‘งโ€๐Ÿ‘ฆWhat is a team?
    • ๐Ÿ›ก๏ธSingle Sign On
    • ๐ŸฅทImpersonate
  • Organisation
    • ๐ŸซWhat is an organisation?
    • ๐Ÿ“คUpload your Organisation logo
    • ๐Ÿ”‘Manage Access to your organisation
    • โ“Understanding Licences
    • ๐Ÿ‘ฎUnderstanding User Roles
  • User Management
    • ๐Ÿท๏ธUser Attributes
    • ๐Ÿ‘ญUser Groups
    • ๐Ÿค–Service Accounts
  • Workspace
    • โœ๏ธWorkspace
    • ๐Ÿ“‚Report Folders
    • โœจSharing & Collaboration
      • Share a report to the Web
    • ๐Ÿ“—Catalog
    • โš™๏ธSettings
  • Warehouse
    • ๐ŸฆConnect your Warehouse
    • โš”๏ธAmazon Athena
      • Connect your Athena
    • ๐ŸฎAmazon Redshift
      • Connect your Redshift
    • ๐ŸงฑDatabricks
      • Connect your Databricks
    • ๐Ÿ”ทGoogle BigQuery
      • Connect your BigQuery
      • Grant access to BigQuery datasets
      • Enable multi project support
    • ๐Ÿ˜Postgres
      • Connect your Postgres
      • Whitelisting Whaly IPs
    • โ„๏ธSnowflake
      • Connect your Snowflake
      • Giving access to Snowflake data
  • Models
    • ๐Ÿ’žModels sync
      • Where should my models be managed?
      • dbt Cloud
        • Configuration
        • Exposing models into Whaly
    • ๐Ÿ“ฅPersistence Engine
      • Configuration
        • Snowflake
      • Check Materialisation runs status
  • Workbench
    • ๐Ÿš€Navigating the workbench
    • ๐Ÿ› ๏ธModeling
      • Understanding Datasets
        • General Information
        • Drills
        • Relationships
        • Primary Keys
        • Cache
      • Model Data
        • SQL Models
        • Flow Models
          • Create a Flow
          • Update a Flow
          • Flow steps
            • From Model
            • From Raw
            • Hide Column
            • Filter
            • Lookup
            • Rollup
            • Formulas
            • Group
            • Union
      • Import raw data
        • From your warehouse
        • From third party data
    • ๐ŸงญExplorations
      • Configure an exploration
      • Exploration Templates
      • Tables
        • Configure a table
        • Add related data
      • Metrics
        • Create a Metric
        • Create a Calculated Metric
        • Create Drill Downs
        • Using custom formatting
      • Dimensions
        • Create a dimension
      • Check measure usage
      • Row Level Access
  • Data consumption
    • ๐Ÿ’กExploring data
      • How to explore data
      • Drill Down
      • Forecasting
    • ๐Ÿ’นWhat is a Report?
    • ๐Ÿ“ŠDashboards
      • Create a dashboard
      • Manage tiles
        • Add chart tiles
        • Add text tiles
        • Add navigation tiles
        • Arranging tiles
      • Add a description
      • Share a dashboard
      • Filter a dashboard
      • Push dashboard
      • Delete a dashboard
    • ๐Ÿ“ˆQuestions
      • Create a question
      • Add a description
      • Share a question
      • Push question data
      • Delete a question
    • ๐Ÿ”Refreshing a report
  • Data visualisation
    • ๐ŸŽจTheming
    • ๐Ÿ–Œ๏ธChart your data
      • Bar chart
      • Calendar chart
      • Funnel chart
      • Gauge chart
      • Geo map chart
      • Heatmap chart
      • Interactive map chart
      • Line chart
      • Metric chart
      • Pie chart
      • Retention chart
      • Table chart
      • Treemap chart
      • Waterfall chart
      • Custom time format in time series
  • Content management
    • โญExplorations Section
    • โœ‚๏ธBulk Content Management
  • Embedding
    • ๐Ÿ“ŒEmbed in Business apps
      • Notion
      • Clickup
      • Hubspot
      • Google Chrome
        • ๐ŸŒฑInstall
        • โš™๏ธConfigure the Chrome extension
    • ๐Ÿ‘ฉโ€๐Ÿ’ปEmbedding API
    • ๐ŸชŸPartner Portal
  • Workflows
    • ๐Ÿš€Push
      • Configure a Push
      • Manage Push
    • ๐Ÿ’ผManage Installed Actions
    • โšกActions catalog
      • Airtable
      • Google Sheets
      • Slack
      • Sendgrid
      • Webhook
  • Platform concepts
    • โœณ๏ธQuery Mode
    • ๐Ÿ’ซCaching
  • Guides
    • โ›‘๏ธSupport
  • User
    • ๐ŸคฉUpload your profile picture
  • Connectors
    • ๐Ÿ”ŒConnect your Sources
    • โš™๏ธWarehouse setup
      • BigQuery
        • Configure a Cloud Storage cleaning rule
      • Snowflake
    • โ˜๏ธWhitelisting Whaly connectors IPs
    • ๐Ÿ”SSH Tunneling
    • ๐Ÿ„Schema drift
    • ๐Ÿ”Replication method
    • ๐Ÿง™Source monitoring
    • ๐ŸŽSource catalog
      • Community
        • Github Stars
        • Slack
        • Orbit
      • Database
        • PostgreSQL / Postgres
          • ๐Ÿ’กTip: Extracting the relationships
        • MariaDB / MySQL
      • eCommerce
        • WooCommerce
      • Engineering
        • Github
      • Finance
        • Brex
        • Pennylane
          • Pennylane (Redshift) - General Ledger & Trial Balance
          • Pennylane API - Customer Invoices
        • Qonto
        • Stripe
        • QuickBooks
      • Marketing / Growth
        • Facebook Ads
        • Google Ads
        • Google Analytics
          • Google Analytics (V4)
          • Google Analytics (Universal Analytics)
        • LaGrowthMachine
        • lemlist
        • LinkedIn Ads
        • Salesloft
      • No-Code
        • Airtable
        • Bubble
        • Google Sheets
      • Support
        • Intercom
      • Product
        • Amplitude
        • MixPanel
        • Segment
      • Sales / CRMs
        • Aircall
        • Pipedrive
        • Hubspot
        • Recruit CRM
        • Salesforce
Powered by GitBook
On this page
  • What does it do?
  • How to use it?
  • How to configure it?
  • Available Formulas
  • URLs operators
  • Date operators
  • Text operators
  • Logical operators
  • Number operators
  • DATETIME formula format
  • When using BigQuery warehouse
  • When using Snowflake Warehouse

Was this helpful?

  1. Workbench
  2. Modeling
  3. Model Data
  4. Flow Models
  5. Flow steps

Formulas

PreviousRollupNextGroup

Last updated 2 years ago

Was this helpful?

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.

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

Format element

Description

Example

%A

The full weekday name.

Wednesday

%a

The abbreviated weekday name.

Wed

%B

The full month name.

January

%b or %h

The abbreviated month name.

Jan

%C

The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99).

20

%c

The date and time representation.

Wed Jan 20 21:47:00 2021

%D

The date in the format %m/%d/%y.

01/20/21

%d

The day of the month as a decimal number (01-31).

20

%e

The day of month as a decimal number (1-31); single digits are preceded by a space.

20

%F

The date in the format %Y-%m-%d.

2021-01-20

%G

2021

%g

21

%H

The hour (24-hour clock) as a decimal number (00-23).

21

%I

The hour (12-hour clock) as a decimal number (01-12).

09

%j

The day of the year as a decimal number (001-366).

020

%k

The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space.

21

%l

The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space.

9

%M

The minute as a decimal number (00-59).

%m

The month as a decimal number (01-12).

01

%n

A newline character.

%P

Either am or pm.

pm

%p

Either AM or PM.

PM

%Q

The quarter as a decimal number (1-4).

1

%R

The time in the format %H:%M.

21:47

%r

The 12-hour clock time using AM/PM notation.

09:47:00 PM

%S

The second as a decimal number (00-60).

00

%s

The number of seconds since 1970-01-01 00:00:00. Always overrides all other format elements, independent of where %s appears in the string. If multiple %s elements appear, then the last one takes precedence.

1611179220

%T

The time in the format %H:%M:%S.

21:47:00

%t

A tab character.

%U

The week number of the year (Sunday as the first day of the week) as a decimal number (00-53).

03

%u

The weekday (Monday as the first day of the week) as a decimal number (1-7).

3

%V

03

%W

The week number of the year (Monday as the first day of the week) as a decimal number (00-53).

03

%w

The weekday (Sunday as the first day of the week) as a decimal number (0-6).

3

%X

The time representation in HH:MM:SS format.

21:47:00

%x

The date representation in MM/DD/YY format.

01/20/21

%Y

The year with century as a decimal number.

2021

%y

The year without century as a decimal number (00-99), with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are 2000s, while years 69-99 are 1900s.

21

%%

A single % character.

%

%E<number>S

Seconds with <number> digits of fractional precision.

00.000 for %E3S

%E*S

Seconds with full fractional precision (a literal '*').

00.123456

%E4Y

Four-character years (0001 ... 9999). Note that %Y produces as many characters as it takes to fully render the year.

2021

When using Snowflake Warehouse

YYYY

Four-digit year.

YY

MM

Two-digit month (01=January, etc.).

MON

Full or abbreviated month name.

MMMM

Full month name.

DD

Two-digit day of month (01 through 31).

DY

Abbreviated day of week.

HH24

Two digits for hour (00 through 23). You must not specify AM / PM.

HH12

Two digits for hour (01 through 12). You can specify AM / PM.

AM , PM

Ante meridiem (am) / post meridiem (pm). Use this only with HH12 (not with HH24).

MI

Two digits for minute (00 through 59).

SS

Two digits for second (00 through 59).

FF[0-9]

Fractional seconds with precision 0 (seconds) to 9 (nanoseconds), e.g. FF, FF0, FF3, FF9. Specifying FF is equivalent to FF9 (nanoseconds).

TZH:TZM , TZHTZM , TZH

Time zone hour and minute, offset from UTC. Can be prefixed by +/- for sign.

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

The year with century as a decimal number. Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %G and %Y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge.

The year without century as a decimal number (00-99). Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %g and %y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge.

The week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is week 53 of the previous year, and the next week is week 1.

Two-digit year, controlled by the session parameter, e.g. when set to 1980, values of 79 and 80 parsed as 2079 and 1980 respectively.

๐Ÿ› ๏ธ
Database Name format
ISO 8601
ISO 8601
ISO 8601
TWO_DIGIT_CENTURY_START