Yandex Cloud
  • Services
  • Solutions
  • Why Yandex Cloud
  • Pricing
  • Documentation
  • Contact us
Get started
Language / Region
© 2022 Yandex.Cloud LLC
Yandex DataLens
  • Getting started
  • Practical guidelines
    • All tutorials
    • Visualizing data from a CSV file
    • Creating and publishing a chart with a map of Moscow from a CSV file
    • Analyzing a retail chain's sales from a ClickHouse database
    • Analyzing public data on road accidents in Russia
    • Analyzing sales and locations of pizzerias based on data from Clickhouse DB and Marketplace
    • Web analytics with a connection to Yandex Metrica
    • Web analytics with funnels and cohorts calculated based on Yandex Metrica data
    • Mobile app analytics based on AppMetrica data
    • Analyzing Yandex Music podcast statistics (for podcasters)
    • Visualizing data with a QL chart
    • Building customer journey charts based on AppMetrica data
  • Concepts
    • Service overview
    • Connection
    • Data types
    • Datasets
      • Overview
      • Data model
      • Dataset settings
    • Charts
      • Overview
      • Chart types
      • Chart settings
    • Dashboards
    • Combining data
    • Using Markdown in DataLens
    • DataLens Public
    • Aggregations in DataLens
    • Calculated fields
      • Overview
      • Formula syntax
    • Marketplace
    • Backups
    • Caching
    • Quotas and limits
    • Organizations in DataLens
  • Step-by-step instructions
    • All instructions
    • Working with connections
      • Creating a ClickHouse connection
      • Creating a connection to a CSV file
      • Creating a Google Sheets connection
      • Creating a MySQL connection
      • Creating a PostgreSQL connection
      • Creating an MS SQL Server connection
      • Creating an Oracle Database connection
      • Creating a YDB connection
      • Creating a Yandex Metrica API connection
      • Creating an AppMetrica connection
      • Creating a Yandex Cloud Billing connection
      • Creating a Greenplum connection
      • Managing connection access
    • Working with datasets
      • Creating a dataset
      • Combining data from multiple tables
      • Combining data from multiple CSV connections
      • Creating a data field
      • Creating a calculated data field
      • Creating a default filter for new charts
      • Updating fields in datasets
      • Describing a dataset via a source SQL query
      • Dataset materialization
      • Managing dataset access
      • Managing access to data rows
    • Working with charts
      • Creating a line chart
      • Creating an area chart
      • Creating a pie chart
      • Creating a ring chart
      • Creating a column chart
      • Creating a bar chart
      • Creating a scatter chart
      • Creating a map
      • Creating a table
      • Creating a pivot table
      • Creating an indicator
      • Creating a tree chart
      • Creating a QL chart
      • Creating a multi-dataset chart
      • Adding a hierarchy
      • Configuring the navigator
      • Publishing a chart
      • Managing chart access
      • Adding guid as a parameter
    • Working with dashboards
      • Creating dashboards
      • Adding charts to dashboards
      • Adding selectors to dashboards
      • Creating an alias
      • Deleting an alias field
      • Publishing dashboards
      • Managing dashboard access
      • Dashboard auto-update
    • Working with permissions
      • Granting permissions
      • Deleting permissions
      • Request permissions
    • Working with DataLens Marketplace
      • Adding a product from Marketplace
      • Removing a product from Marketplace
      • Creating and using a connector (for partners)
    • Working with organizations
      • Switching between DataLens instances
  • Access management
    • Managing access to DataLens
    • Managing access at the data row level
  • Pricing policy
  • Function reference
    • All Functions
    • Aggregate functions
      • Overview
      • ALL_CONCAT
      • ANY
      • ARG_MAX
      • ARG_MIN
      • AVG
      • AVG_IF
      • COUNT
      • COUNTD
      • COUNTD_APPROX
      • COUNTD_IF
      • COUNT_IF
      • MAX
      • MEDIAN
      • MIN
      • QUANTILE
      • QUANTILE_APPROX
      • STDEV
      • STDEVP
      • SUM
      • SUM_IF
      • TOP_CONCAT
      • VAR
      • VARP
    • Array functions
      • Overview
      • ARRAY
      • ARR_STR
      • CONTAINS
      • COUNT_ITEM
      • GET_ITEM
      • SLICE
      • STARTSWITH
      • UNNEST
    • Date/Time functions
      • Overview
      • DATEADD
      • DATEPART
      • DATETRUNC
      • DAY
      • DAYOFWEEK
      • HOUR
      • MINUTE
      • MONTH
      • NOW
      • QUARTER
      • SECOND
      • TODAY
      • WEEK
      • YEAR
    • Geographical functions
      • Overview
      • GEOCODE
      • GEOINFO
      • TOPONYM_TO_GEOPOINT
      • TOPONYM_TO_GEOPOLYGON
    • Logical functions
      • Overview
      • CASE
      • IF
      • IFNULL
      • ISNULL
      • ZN
    • Mathematical functions
      • Overview
      • ABS
      • ACOS
      • ASIN
      • ATAN
      • ATAN2
      • CEILING
      • COS
      • COT
      • DEGREES
      • DIV
      • EXP
      • FLOOR
      • GREATEST
      • LEAST
      • LN
      • LOG
      • LOG10
      • PI
      • POWER
      • RADIANS
      • ROUND
      • SIGN
      • SIN
      • SQRT
      • SQUARE
      • TAN
    • Operators
      • Overview
      • AND
      • Addition and concatenation (+)
      • BETWEEN
      • Comparison
      • Division (/)
      • IN
      • IS FALSE
      • IS TRUE
      • LIKE
      • Modulo (%)
      • Multiplication (*)
      • NOT
      • Negation (-)
      • OR
      • Power (^)
      • Subtraction (-)
    • String functions
      • Overview
      • ASCII
      • CHAR
      • CONCAT
      • CONTAINS
      • ENDSWITH
      • FIND
      • ICONTAINS
      • IENDSWITH
      • ISTARTSWITH
      • LEFT
      • LEN
      • LOWER
      • LTRIM
      • REGEXP_EXTRACT
      • REGEXP_EXTRACT_NTH
      • REGEXP_MATCH
      • REGEXP_REPLACE
      • REPLACE
      • RIGHT
      • RTRIM
      • SPACE
      • SPLIT
      • STARTSWITH
      • SUBSTR
      • TRIM
      • UPPER
      • UTF8
    • Text markup functions
      • Overview
      • BOLD
      • ITALIC
      • MARKUP
      • URL
    • Time series functions
      • Overview
      • AGO
      • AT_DATE
    • Type conversion functions
      • Overview
      • BOOL
      • DATE
      • DATETIME
      • DATETIME_PARSE
      • DATE_PARSE
      • DB_CAST
      • FLOAT
      • GEOPOINT
      • GEOPOLYGON
      • INT
      • STR
    • Window functions
      • Overview
      • AVG
      • AVG_IF
      • COUNT
      • COUNT_IF
      • FIRST
      • LAG
      • LAST
      • MAVG
      • MAX
      • MCOUNT
      • MIN
      • MMAX
      • MMIN
      • MSUM
      • RANK
      • RANK_DENSE
      • RANK_PERCENTILE
      • RANK_UNIQUE
      • RAVG
      • RCOUNT
      • RMAX
      • RMIN
      • RSUM
      • SUM
      • SUM_IF
    • Function Availability
  • Tutorial on functions
    • Aggregate functions
    • Window functions
    • LOD expressions and filtering in aggregate functions
  • Public materials
    • Educational projects
    • Webinars and conferences
    • Articles and publications
    • Public dashboards and charts
  • Troubleshooting
    • Questions and answers
    • DataLens errors
  1. Concepts
  2. Calculated fields
  3. Formula syntax

Formula syntax

Written by
Yandex Cloud
  • Dataset fields in calculations
  • Constants
  • Operators
  • Formatting formulas
  • Comments
  • Logical operations
  • Strings
  • Converting types
  • Aggregation
  • Text markup

The syntax of calculated expressions in Yandex DataLens is similar to SQL.
Simple expressions can be described using ordinary arithmetic operations:

([Sales] - [Profit]) / 10
[Date] - #2019-01-24#

More complex expressions use multiple functions to implement various calculations, aggregations, and conversions of data from one type to another:

CONCAT(SUM([Category Sales]) / [Total Sales], ' %')
DATETRUNC([datetime], 'month')

Dataset fields in calculations

The syntax used for accessing dataset fields is similar to Transact-SQL, but in Yandex DataLens, the field name must be enclosed in square brackets ([]):

[Field name]

Constants

In addition to fields, operators, and functions, expressions can include constants of different data types:

  • Integer: 23, -4325653.
  • Fractional number: 0.0234, -1.0.
  • Date: #2020-01-01#.
  • Date and time: #2020-01-01 11:15:00#.
  • String: "String".
  • Boolean: TRUE, FALSE.
  • Geopoint: GEOPOINT("[55.7912,37.6872]").

Operators

Operators available in expressions:

  • Arithmetic: +, -, *, /.

    ([Sales per Order] * [OrderCount]) - [Profit]
    
    ([Profit] / [Cost Price]) * 100
    
    [City] + " " + "city"
    

    Addition (+), subtraction (-), and multiplication (*) operators behave differently depending on the argument type.

  • Exponentiation: ^.

    [Mass] * [Speed] ^ 2
    
  • Remainder of a division: %.

    [Sales] % 10 + [Cost Price] % 10
    
  • Boolean: AND, OR, NOT, IN, LIKE, IS TRUE, IS FALSE, and BETWEEN.

  • Comparisons: =, !=, <, <=, >, and >=.

    Comparison operators let you create logical chains:

    1 > x > -4 > y != 8
    

Full description of all operators.

Formatting formulas

Any formula can be written in one or several lines:

CONCAT(
    SUM([Category Sales]) / [Total Sales],
    ' %'
)

Comments

Comments are used to add explanations or to ignore parts of formulas:

  • One-line comment.

    -- This is a one-line comment
    
  • Block comment.

    /* This is a block
    comment */
    

Logical operations

Logical functions are used for branching calculations in expressions:

  • CASE.

    CASE [ProductID]
        WHEN 1 THEN "Bananas"
        WHEN 2 THEN "Apples"
        WHEN 3 THEN "Pears"
        ELSE "Other"
    END
    
    CASE( 
        [Color],
        "R", "Red",
        "G", "Green",
        "B", "Blue",
        "Not RGB" 
    )
    

    Full description of the CASE logical function.

  • IF.

    IF([MassIndex] BETWEEN 18.5 AND 25, "Normally", "Not normal")
    
    IF
        [Year] % 400 = 0 OR ([Year] % 4 = 0 AND [Year] % 100 != 0)
            THEN "Leap year"
        ELSE "Ordinary year"
    END
    
    IF
        [City] = "Moscow"
            THEN "This is the Capital"
        ELSEIF [City] = "St. Petersburg"
            THEN "This is the northern Capital"
        ELSE "Other city"
    END
    

    Full description of the IF logical function.

  • IFNULL, ISNULL, and ZN.

    IFNULL([Cost Price], 10) * [OrderCount]
    

    Full description of the IFNULL logical function.

    IF(ISNULL([Product Name]) = TRUE, "Unnamed", [Product Name] + " " + [ProductID])
    

    Full description of the ISNULL logical function.

    ZN([Total Sales]) - ZN([Total Cost])
    

    Full description of the ZN logical function.

Strings

The following string functions are used for processing text data:

CONCAT([Total Sales], "$")
IF(CONTAINS([Product Name], "RU"), [Product Cost] + " " + "RUB", [Product Cost] + " " + "USD")
REPLACE([OrderID], "2020", [Month])
IF(STARTSWITH([Region Name], "RU_"), SPLIT([Region Name], "_", 2), [Region Name])

Strings can be enclosed in single or double quotes. In this case, one type of quotation mark can be used inside the other:

FIND([Product Name], 'plus')
CONCAT('"', [Product Name], '"')

You can make different conversions of string data using special characters in formulas:

REPLACE([ShopAddress], "\n", " ")
"File path" + " = " + "\\" + [Folder] + '\\' + [Filename]

Note

Special characters such as \n, \t, and \r do not affect the display of the source data.

Converting types

Expression values can be converted from one type to another:

FLOAT([StringWithNumbers])
DATETIME(STR([Order Date]) + "-" + STR([Order Time]))
GEOPOINT([Latitude],[Longitude])

Full description of the type conversion functions.

Aggregation

To calculate the resulting values, use aggregate functions:

AVG([Check Total]) * COUNTD([CustomerID])
SUM_IF([Sales], [Category] = "Fruits")
IF
    MIN[Date] = #2020-01-01#
        THEN SUM[Sales] * 1.1
    ELSE SUM[Sales] * 1.2
END

Window functions let you aggregate values from a group of strings, without combining these strings into one. This distinguishes them from aggregate functions. You can also use window functions to calculate values for one string in the context of values from other strings.

SUM([Sales] WITHIN [PaymentType]) / SUM([Sales] TOTAL)
MSUM([Sales per Order], 1 WITHIN [ProductID] ORDER BY [Price])
RANK_DENSE(AVG([Price]), "desc" WITHIN [ShopID] BEFORE FILTER BY [PaymentType])

Window functions support grouping and sorting of records and the BEFORE FILTER BY option.

Text markup

To create text with markup, use markup functions:

MARKUP(BOLD("This"), " ", ITALIC("is"), " ", URL("https://example.com/", [LinkName])))

To use numeric constants when marking up URLs, convert them to the String type:

URL("https://example.com/?value=" + STR([Value]), [Value]))

Was the article helpful?

Language / Region
© 2022 Yandex.Cloud LLC
In this article:
  • Dataset fields in calculations
  • Constants
  • Operators
  • Formatting formulas
  • Comments
  • Logical operations
  • Strings
  • Converting types
  • Aggregation
  • Text markup