lag and offset function

Donna Marie
edited February 2023 in Modeling

can anyone explain the use of  this formula

 

if not sys01 time settings by week. 1st forecast yr? then Lag(default forecast, 52, 0 strict) else Dat03 historic volumes.offset volume for 1st forecast yr?

 

 

Comments

  • @Donna Marie 

     

    Sure! Let's try to break it for you:

     

    Formula: if not sys01 time settings by week. 1st forecast yr? then Lag(default forecast, 52, 0 strict) else Dat03 historic volumes.offset volume for 1st forecast yr?

    Meaning: If it is not First forecast year (i.e., it is NOT 2020) then get me the Values of Default forecast line item from same week of the last year Else get me Offset Volume from DAT03 module.

     

    Here you are trying to have values in both 2020 and 2021. 

    Dat03 historic volumes.offset volume for 1st forecast yr? - Feeds 2020 This Line items uses OFFSET to pull the values from 2019 to 2020 as a Baseline without uplifting the 2019 numbers.

    if not sys01 time settings by week. 1st forecast yr? then Lag(default forecast, 52, 0 strict) - Feeds 2021 This line item uses LAG to pull the values from 2020 to 2021 after uplifting the baseline numbers with Growth Rate%. 

    Instead of LAG you could use OFFSET in this formula but the additional feature ( STRICT Mode at the end) that you have with LAG basically weighs in here.

     

    Misbah

     

     

     

  • Am confused now with this formula

     

    IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' = TRUE THEN 'DAT03 Historic Volumes'.'Offset Volumes for 1st Forecast Year' ELSE OFFSET(Default Forecast, -52, 0)

     

    breaking into parts:

    (1) IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' = TRUE

    [if it's baseline for  2020, then follow the offset vols used in DAt03 historic volumes module: where 2020 uses 2019 vols]

    (2) If not it's baseline for 2020 [i.e. for 2021 baseline], offset using default values from 2020 

     

    how does this work out the same values but using Lag - not understanding Lag (lag doesnt use -52)

  • @Donna Marie 

     

    LAG will use 52 where as OFFSET will use -52 to throw the same results. However you should use LAG 52 in this scenario.

    Here is the thread which basically explains the performance comparison between these functions

    https://community.anaplan.com/t5/Anaplan-Platform/Performance-Comparison-OFFSET-LAG-PREVIOUS-LOOKUP/m-p/51140#M7603

     

    Misbah

  • I am using below formula, but I'm not getting any value in week 1 FY20. Can you please advise what I am doing wrong? thanks

    IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN LAG(Volumes, 52, 0, STRICT) ELSE 0

    patelpn3_0-1625243710093.png