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
    • Parameterization
    • 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
      • Adding parameters to a dataset
    • Working with charts
      • Creating a line chart
      • Creating an area chart
      • Creating a pie chart
      • Creating a donut 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
      • Adding parameters to a chart
    • 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_AVG
      • ARR_MAX
      • ARR_MIN
      • ARR_PRODUCT
      • ARR_STR
      • ARR_SUM
      • CAST_ARR_FLOAT
      • CAST_ARR_INT
      • CAST_ARR_STR
      • CONTAINS
      • COUNT_ITEM
      • GET_ITEM
      • REPLACE
      • 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
      • COMPARE
      • 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. Function reference
  2. Time series functions
  3. Overview

Time series functions

Written by
Yandex Cloud
  • Syntax
    • BEFORE FILTER BY
    • IGNORE DIMENSIONS
  • AGO
  • AT_DATE

Time series functions provide various ways to look up values corresponding to a specific time or offset along a given time axis.

In a certain way this functionality is similar to the window function LAG.
The main difference is that LAG is indifferent to the actual values of the dimensions being used, and operates over positional offsets specified in rows, while time series functions use specific values and value offsets in date/time units like days, hours or seconds. This makes them sensitive to missing values in data. As a result of this AGO(SUM([Sales]), [Date], "year") will return NULL if the same-date row for the previous year is missing.

Syntax

Time series functions support extended syntax:

<FUNCTION_NAME>(
    arg1, arg2, ...

    [ BEFORE FILTER BY filtered_field_1, ... ]
    [ IGNORE DIMENSIONS dimension_1, ... ]
)

BEFORE FILTER BY

If any fields are listed in BEFORE FILTER BY, then the function is calculated before data is filtered using these fields.
Let's say you are trying to calculate the value of [Sales] a year ago from today's date:

AGO([Sales], [Date], "year", 1)

and also have a filter in the chart limiting the dates to a specific year (2018 < [Year] <= 2019, where Year is calculated as YEAR([Date])). All data pertaining to the year 2018 will be omitted from the result. Because of this the function will return NULL.

If BEFORE FILTER BY is added to the function:

AGO([Sales], [Date], "year", 1 BEFORE FILTER BY [Year])

it will return the value of [Sales].

The date/time dimension specified as the second argument of time series functions is implicitly added to the BEFORE FILTER BY clause. For example, the following to formulas are equivalent:

AGO([Sales], [Date], "week")
AGO([Sales], [Date], "week" BEFORE FILTER BY [Date])

IGNORE DIMENSIONS

The IGNORE DIMENSIONS clause allows the exclusion of dimensions from the search criteria. When looking up a value for a certain date, the search is done by matching dimension values in each row against the values of the same dimensions in the original row. If any of the other dimensions correlate with the date dimension, then the data query may return an empty result (NULL).

For example, if the data request contains [Date], [Month] and AGO([Sales], [Date], "month"), then it will be impossible to find a row with [Date] being a month earlier, but the value of [Month] being the same as in the current row. This will result in AGO always returning NULL.

To get the correct value of [Sales] exclude [Month] using the IGNORE DIMENSIONS clause:

AGO([Sales], [Date], "month" IGNORE DIMENSIONS [Month])

AGO

Syntax:
AGO( measure, date_dimension [ , unit [ , number ] ] )
or
AGO( measure, date_dimension [ , unit [ , number ] ] [ BEFORE FILTER BY ... ] [ IGNORE DIMENSIONS ... ] )

Re-evaluate measure for a date/time with a given offset.
The date_dimension argument is the dimension along which the offset is made.
The number argument is an integer. It can be negative.
The unit argument takes the following values:

  • "year";
  • "month";
  • "day";
  • "hour";
  • "minute";
  • "second".

Can also be used as AGO( measure, date_dimension, number ). In this case, the third argument is interpreted as the number of days.

See also AT_DATE, LAG.

AT_DATE

Syntax:
AT_DATE( measure, date_dimension, date_expr )
or
AT_DATE( measure, date_dimension, date_expr [ BEFORE FILTER BY ... ] [ IGNORE DIMENSIONS ... ] )

Re-evaluate measure for a date/time specified by date_expr.
The date_dimension argument is the dimension along which the offset is made.

See also AGO, LAG.

Was the article helpful?

Language / Region
© 2022 Yandex.Cloud LLC
In this article:
  • Syntax
  • BEFORE FILTER BY
  • IGNORE DIMENSIONS
  • AGO
  • AT_DATE