LAG function - date format

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

Accepted answers

All comments

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

Quick Links

Can you help?

Unanswered questions

Different Subsets of Parent Lists in Input & Output Modules
I have 2 lists used in modelling, In Input module I'm using subset of territories from Country List and subset of Regions from Flat list. But in Output module,Its reversed, I'm using subset of regions from Country list and subset of territories from flat list.How to derive the numbers from input to output module. I do have…
Is there a way to turn off the Show Comments Summary panel in UX pages with Comments?
This shows all comments across all data to all users with access to the UX page. This is not appropriate for any UX which might contain security access sensitive data. Comments on grids and cards ARE security dependent. That works fine. But anyone with access to the page can open this Summary Panel and see ALL comments…
Are there any plans to support actions to rename or delete native Versions?
If anyone is aware of idea exchange ideas that are currently being evaluated by the product team, if you don't mind linking them here, would love to give them an upvote. Thanks!