OFFSET v LAG v LEAD
I understand each of these functions but could someone succinctly explain the difference.
I would naturally lean towards using OFFSET when looking to pull and push data from different time periods so I have never actually used LAG or LEAD in a live build.
Where would LAG or LEAD be used over OFFSET?
@ChrisAHeathcote I would say to use LAG or LEAD only when you want to consider the performance.
see below link with a comparison between different functions:
Hope it helps.
Hi @ChrisAHeathcote ,
My understanding is a key difference between the three is that LEAD and LAG can be used in STRICT mode allowing you to avoid circular references, whereas OFFSET cannot. PREVIOUS and NEXT also allow you to avoid circular references as well if you are only trying to pull one period forward or backward and the performance is better.
Example where OFFSET would not achieve the same effect: https://community.anaplan.com/t5/Anaplan-Platform/Circular-References-OFFSET/m-p/67511#M12845
in 6 years of Anaplan, I have never used offset. I use the equivalent dedicated function (lag, previous, next).
Lag works for future and past so it does everything.
Performancewise, offset/lag are very close, with previous being the fastest
The link is really interesting and given that performance is comparable between OFFSET and LAG I will most likely continue to use OFFSET.
I do agree that if I need to retrieve a value one time period either side I will use PREVIOUS or NEXT as there is a clear performance advantage over other functions.