OFFSET on LIST ITEM

Unicorn

When entering the formulae below it works,however if i change the -1 in the OFFSET formulae to point to a lineitem to pass the no of periods to offset it is formatted as a number anaplan gives an error , the AutopopulateActivity line item is fomatted as a list item.

IF ISNOTBLANK(Select Job Code) THEN Select Job Code ELSE OFFSET(AutoPopulateActivity, -1, BLANK)

 

 

 

nathan_rudman

Hi,

 

Interesting. You cannot use blank when you specify a dynamic argument. We had this issue with movingsum a while ago, I reported the bug and it was corrected, seems it is the same here.

You could create a placeholder or blank element in the list and specify it in the formula, or what I did, create a list formatted line item with no value (and no dimension applied to it, not even time)

 

IF ISNOTBLANK(Job Code) THEN Job Code ELSE OFFSET(Job Code, 'Counter -1', 'List Formatted Blank')

 

Now the reason why Anaplan consider it differently is that this formula is going to make you a circular reference. While the OFFSET -1 behaves like a previous(), which avoids the circular reference, it seems OFFSET -Dynamic does not. You'll notice in my formula I OFFSET the Job Code, not AutopopulateActivity. Which from what I understand gives the same result.

 

Finally, if I understand your goal correctly, I'd just write this like this:

IF isblank(Job Code) then previous(AutopopulateActivity) else Job Code