LAG function constraints don't allow for some valid scenarios


I would like to submit an enhancement request for the LAG function to be used between time ranges that are not the same but have valid overlap.


The constraint that disallows the use of LAG between any line items that do not have the same time range gives the function minimal utility. I understand disallowing instances where the timescales do not match (i.e. trying to pull data from nowhere), such as using a current-year time range to pull from a future-year time range, but not allowing use where that is not the case (e.g. LAG(Prior Year Line Item, 12, 0) on a current-year time range) is unduly handicapping.


As an example, a function that would be: LAG('Metrics'.Actual, 36, 0)


Has now become: 



This greatly simplifies the ability to reference previous periods from other line items/modules, eliminating the need to repeat PREVIOUS() functions and allows for overflow calculations.

1 votes

New · Last Updated


  • Please don't do that!!

    The workaround is to use a time range that is the same length or greater than the offset value


    From the following Anapedia page


    Hard-coded Offset

    Generally for time series functions the time ranges of the result line item and all line item arguments must conform.

    There is an exception to this, with the functions LEAD, LAG, OFFSET, and POST. For these functions, the value argument can have a different time range, if:

    • Offset argument is a value (a hard-coded number, rather than a reference to a line item).
    • Offset value is greater than or equal to the number of periods in result time range.

    The system tests whether the offset value points to periods entirely outside the time range of the result item. If this is the case, the value argument can have any time range, and the functions then return the value specified by the fill argument (or zero in the case of POST, which does not have a fill argument).

    Note that for LEAD/LAG and OFFSET, the fill argument must match – a calculation can never be applied if the fill and result line items use different time ranges.

  • Miran
    Status changed to: New

Get Started with Idea Exchange

See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!