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:

    • measureAny
    • date_dimensionDate | Datetime
    • unitInteger | String
    • numberInteger

    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.