PREVIOUSITEM() - PREVIOUS() on a list

peter_mcanena

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.

nathan_rudman

Hi,

 

I don't know if you're looking for it, but there's a way to do it currently, which works, albeit a bit complicated:

You can rank your list elements from 1 to x. then you create a module / line item over time, with a daily calendar. With your calculated rank, you can assign every element to a specific day. Then you are able to do all the time functions (previous included) on those line items.

peter_mcanena

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

[Deleted User]

A solve to this, without needing to use the time dimension, would be to create a seperate identical version of the list you are trying to reference, and enter a sequential range of numbers into the code. Then in a module dimensionalized by the original list, create a CODE(FINDITEM(List2, NAME(ITEM(List1)). This will find the item in the second list, and return the sequential number. Then subtract 1 from the VALUE of the code. Then FINDITEM on the CODE-1 line item to return the list item name. Then FINDITEM on the item name to return to your original list. This will give you the previous item. 

Nitika

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

yelena_keselman

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.

 

nathan_rudman

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.