PREVIOUSITEM() - PREVIOUS() on a list

Referring to the previous item in a list is a nightmare: You can write a lookup to find the previous item, but as soon as you try to use it you get a circular reference. Sometimes you can get round this with creative use of the CUMULATE() function on your list, but sometimes you can't and you need to map your calculation onto the time dimension.

 

But if CUMULATE() can be used on a list, why not PREVIOUS()? It would be awesome to have a PREVIOUSITEM() function that allowed you to pick up a value from the previous item in a list, while letting Anaplan know that you're not creating a circular reference. You can be sure it's not a circular reference because the dependencies would always flow in the direction of the list, and it would solve all sorts of problems.

 

We're always saying "If you can do something in Excel, you can do it in Anaplan," but recursion is an example (possibly the only example?) of something that's simple in Excel but virtually impossible in Anaplan. Excel can handle recursive calculations on any dimension, but Anaplan can only really handle recursion over the time dimension. This would solve that.

 

PREVIOUSITEM(x, y) where

  • x: Any expression
  • y: List

The expression x must be based on the list y. PREVIOUSITEM(x, y) would then pick up the value of x from the previous item in the list y.

 

Anaplan would need to allow PREVIOUSITEM(x, y) to be used in the definition of x itself, in the same way as PREVIOUS(), without creating a circular reference.

Answers

  • Hi Nathan,

     

    Yes, this is a neat solution. It would be great if you could do the whole thing without resorting to the time dimension though!

     

    Pete

  • Hi @avander8 ,

     

    I tried the below solution, that is definitely a good option. But some how we have another property for PREVIOUS() that it can be applied to the same line item. for ex: X = 1+Previous(X). when we use the below procedure we miss that facility provided by PREVIOUS().

     

    I am not sure if I am missing any intermediate step. Please let me know.

     

    Thanks

  • A previousitem() or nextitem() would be the ideal solution here. I like @avander8 's solution, I think it's easy to understand and set up. If you want to avoid using the time dimension and do not want to update the code column, here is another solution you can use.

     

    Step 1: Create a dummy list for "position" this is numbered lets say 1....x (For x, you be the judge based on the size and scope of the list members you need to compare, in my example I used 1-20). 

     

    Step 2: Use a combination of the RANK function and FINDITEM to identify the list members that sit minus 1 and plus 1 away from each respecting  list member. Screenshots and blue prints attached for guidance. 

     

    I have also recorded a video using a project timeline use case example with this solution in action. Although it's more investment for setup, the experience for the end user feels seamless.

     

  • time is the only solution to avoid the circular reference.
    What avander and Yelena expose here only allow you to bring 1 previous value. We use Yelena's solution often enough (especially for list settings such as brackets with lower- upper bounds),  it takes 2 minutes to set up, but it's not replacing previous.