LAG function - date format

MoraruD

Hello Anaplan Community,

I've encountered an error message that's been causing some confusion for me.

I'm sure I'm missing something crucial with the LAG function, and I'd greatly appreciate your assistance in resolving this issue.

As far as I understand, the LAG function in Anaplan is used to return values from a prior period. The value to offset can be of any format. My specific aim is to use this function to retrieve a date from the past, and in our model, time scale is set to Day. I've attached a screenshot of the simplified model for reference.

The expression I intended to use in the LAG function is as follows: LAG(Date to Replace with, Offset Amount, Blank).

However, when I attempt to implement this expression, I'm encountering an error message (see bellow image).

Could someone kindly help me understand what might be wrong with my formula and provide guidance on how to correct it?

I want to thank you in advance for your assistance in resolving this issue.

Looking forward to your guidance.

Best regards,
Dumitrita

rob_marshall

@MoraruD

You were almost there…Instead of using the cumulate line item as the second parameter in the LAG function, replace it with a 1 or a 2, basically a constant. That will get it working, but there is a better way to do what you need, I believe.

First, it is not performant to use the cumulate function with the Timescale, especially at the Day level. Instead, of using Cumulate, try using Previous. And instead of the lag function, try using MovingSum.

pyrypeura

Hi,

You could get to same result with OFFSET as in below

MoraruD

Thank you both for your answers.

@pyrypeura
Looks like the offest amount works with dates, wich is not the case with LAG function.

@rob_marchal
My goal is to retrieve the latest available input based on a boolean line item (input value). To elaborate, if there's no input value recorded on a particular date, I aim to display the most recent available input instead. The challenge here is that the number of periods I need to consider when searching for this past value may vary depending on the scenario. Maybe this excel exemple could help you understand my use case.

In my initial attempt, I opted for the CUMULATE function (althought I understand this goes against the rules) because it allows me to reset the cumulative sum using a boolean condition (in my case, the "Input?" line item). However, I'm uncertain about how to achieve the same result using the MOVINGSUM function.

Thanks,
Dumitrita

rob_marshall

@MoraruD

So Previous would work great here and be the most performant. The formula for Final Value would be: if value <> 0 then Value else previous(Final Value).