Retrieve a max value from given dynamic periods

Hi, Anaplaners

 

I posted a similar question before (https://community.anaplan.com/t5/Best-Practices/Time-where-a-value-is-max/m-p/40577#M4185), but this time I have faced an issue that cannot be solved by the solution I get to received in the previous question. Thing is that (see the attached) the line item "MAX Value" see its value and values of next two months to fetch a max value. (For example, FY19 Jan sees FY Jan, Feb, and Mar to get a max value of the three months, and FY19 Feb sees FY19 Feb, Mar, and Apr) Can you give me pointers to solve this ?

Thanks in advance

Regards,

Greg 

Best Answer

  • HI Gregory,

     

    You can bring it in this way. Create 2 line items and apply offset formula to get current period +1 and +2 and then in your Max value line item refer Max(source line,value +1,value +2) ,you will get the answer.Retrieve a max value from given dynamic periods.PNG

    Retrive Max.PNG

     

     

     

    Thanks,

    Kavin

     

Answers

  • Thanks, Kavin.

    A simple solution but perfectly worked for me !!

  • Hi Gregory, As alternative to OFFSET function you can try to use function Next (), so the final formula could be like this: Max Value = MAX(Value, NEXT(Value), NEXT(NEXT(Value)) )