Retrieve a max value from given dynamic periods

Contributor

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 

3 REPLIES 3
Highlighted
Frequent Contributor

Re: Retrieve a max value from given dynamic periods

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

 

Contributor

Re: Retrieve a max value from given dynamic periods

Thanks, Kavin.

A simple solution but perfectly worked for me !!

Occasional Contributor

Re: Retrieve a max value from given dynamic periods

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)) )