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 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.

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 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.

Previous
Overview
Next
AT_DATE