AGO

Syntax

AGO( measure, date_dimension [ , unit [ , number ] ] )

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

More info:

Description

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 sets the offset in units of the unit argument. Set as an integer. It may take negative values. The default value is 1.
The unit argument sets the unit for number. It may take the following values:

  • "year";
  • "month";
  • "week";
  • "day" (default value);
  • "hour";
  • "minute";
  • "second".

Can also be used as AGO( measure, date_dimension, number ). In this case, the unit argument takes the "day" value.

See also AT_DATE, LAG.

Argument types:

  • measure — Any
  • date_dimension — Date | Datetime
  • unit — Integer | String
  • number — Integer

Return type: Same type as (measure)

Note

Only constant values are accepted for the arguments (unit, number).

Note

The first argument must be a measure (aggregated expression), otherwise an error will be raised.

Examples

AGO(SUM([Sales]), [Order Date], "month", 3)

AGO(SUM([Sales]), [Order Date], "year")

AGO(SUM([Sales]), [Order Date], 1)

Data source support

ClickHouse 21.8, Yandex Metrica, Microsoft SQL Server 2017 (14.0), MySQL 5.7, Oracle Database 12c (12.1), PostgreSQL 9.3, YDB.

Previous
Overview
Next
AT_DATE