OFFSET v LAG v LEAD

ChrisHeathcote
Community Boss

OFFSET v LAG v LEAD

Hi All,

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?

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
2 ACCEPTED SOLUTIONS

Accepted Solutions
alexpavel
Certified Master Anaplanner

Re: OFFSET v LAG v LEAD

@ChrisHeathcote  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:

 

https://community.anaplan.com/t5/Anaplan-Platform/Performance-Comparison-OFFSET-LAG-PREVIOUS-LOOKUP/...

 

Hope it helps. 

Alex

 

 

View solution in original post

allison_slaught
Certified Master Anaplanner

Re: OFFSET v LAG v LEAD

Hi @ChrisHeathcote ,

 

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 

 

Best,

Allison

View solution in original post

4 REPLIES 4
alexpavel
Certified Master Anaplanner

Re: OFFSET v LAG v LEAD

@ChrisHeathcote  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:

 

https://community.anaplan.com/t5/Anaplan-Platform/Performance-Comparison-OFFSET-LAG-PREVIOUS-LOOKUP/...

 

Hope it helps. 

Alex

 

 

View solution in original post

nathan_rudman
Master Anaplanner/Community Boss

Re: OFFSET v LAG v LEAD

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

 

https://community.anaplan.com/t5/Anaplan-Platform/Performance-Comparison-OFFSET-LAG-PREVIOUS-LOOKUP/...


Nathan Rudman, Anaplan Model Builder
ChrisHeathcote
Community Boss

Re: OFFSET v LAG v LEAD

@alexpavel and @nathan_rudman 

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. 

Thanks, 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
allison_slaught
Certified Master Anaplanner

Re: OFFSET v LAG v LEAD

Hi @ChrisHeathcote ,

 

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 

 

Best,

Allison

View solution in original post