AGO

SyntaxSyntax

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

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

DescriptionDescription

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)

ExamplesExamples

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

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

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

Data source supportData source support

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