Understanding LAG Function

Misbah
Moderator

Understanding LAG Function

LAG

Definition: Returns the value from the past or from the future based on the syntax

Constraint: TIME & DATE Function. LAG will work only with Native TIME.

Syntax: LAG(X,Y,Z,O)

ArgumentsDescriptionLong DescriptionNatureFormat
XValue to OffsetValue to replace with a value from
a different time period.
MandatoryAny (Number, Boolean, Text, List, Time Period, Dates)
YOffset AmountNumber of periods past, from which to retrieve a value. Positive values refer to past periods, negative to future ones and Zero is the current period.MandatoryNumber
ZSubstitute ValueValue to apply if the Offset amount specifies a period outside of the model's time range.
Also used for non-positive offsets if you use the SEMISTRICT or STRICT keywords for the Non-positive behavior argument.
MandatoryAny, based on X argument's format
ONon Positive BehaviorDetermines if the LAG function should use Substitute Value if the Offset Amount is negative or zeroOptionalSTRICT, SEMISTRICT, NONSTRICT

If you don't provide the fourth argument of syntax then the default behavior would be NONSTRICT.

STRICT: Returns the Value (X) if the Offset Value (Y) is positive. If (Y) is negative then it returns the Substitute Value(Z)

SEMISTRICT: Returns the Value (X) if the Offset Value (Y) is positive or zero. If (Y) is negative then it returns the Substitute Value(Z)

NONSTRICT: Returns the Value (X) in all cases. You don't need to provide this in an argument because it is considered to be Default behavior of Anaplan.

Now let's try to understand how the outputs change based on the arguments used

Scenario I: Hardcoding the Offset Amount. 

In this example since the hardcoded value is +2 it pushes the Value two periods towards right or in another words it brings data from 2 periods in the past to the current period. Q1FY20 and Q2FY20 should have received inputs from Q3FY19 and Q4FY19 respectively but since the timescale starts from Q1FY20 here comes the concept of Substitute Value which is 0 in this case and that is the reason Q1 & Q2 have 0 as an output

Misbah_0-1621942627894.png

Scenario II: Variable Offset & Substitute value using STRICT Mode. 

In this example we are using STRICT Mode which means that if the Offset value is negative or zero then the Substitute value will be displayed otherwise it will bring the Value based on the Offset Amount. Red ones are where Substitute Values are being displayed  & the Green ones are where Values based on the Offset Amount are being shown

Misbah_0-1622013341619.png

 

Scenario III: Variable Offset & Substitute value using SEMISTRICT Mode.

In this example we are using SEMISTRICT Mode which means that if the Offset value is negative then the Substitute value will be displayed otherwise it will bring the Value based on the Offset Amount.

Misbah_5-1621943694968.png

Scenario IV: Variable Offset & Substitute value using NONSTRICT Mode.

In this example we are using NONSTRICT Mode which means Substitute value will not be displayed

Misbah_6-1621943791324.png

Below grid for comparison of three modes

Misbah_7-1621943966701.png