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)
Arguments | Description | Long Description | Nature | Format |
X | Value to Offset | Value to replace with a value from a different time period. | Mandatory | Any (Number, Boolean, Text, List, Time Period, Dates) |
Y | Offset Amount | Number 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. | Mandatory | Number |
Z | Substitute Value | Value 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. | Mandatory | Any, based on X argument's format |
O | Non Positive Behavior | Determines if the LAG function should use Substitute Value if the Offset Amount is negative or zero | Optional | STRICT, 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
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
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.
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
Below grid for comparison of three modes
Categories
- All Categories
- 10.2K Forums