How to sum a previous period value and current period value (multiple times)

LucaRicci

Hi all,

Have been trying to sum the values from the last week and the current week to obtain a new cumulative value; this value will be the new value to be added to the current week value.

Unfortunately, this does not seem to be so easy in Anaplan.

WEEK1

I start the first week with one value (layers-actual) and, of course, the value from the previous week is 0 but it actually comes from a LOOKUP[previous week] formula. I sum these two and I get the layers - cumulative line item.

WEEK2

I start the week with a new value which is to be added to the lookupped value I get from the first week. I sum these two and I get the new cumulative value.

So far so good.

WEEK3

Now, as usual I have the new value for the week which needs to be added to the cumulative value from the last week. However, every attempt I made fails to work due to circular reference.

My formula would be for the cumulative line item:

IF WEEK=1 OR WEEK=2

THEN layers - actual + layers - previous week

ELSE layers - actual + layers - cumulative[LOOKUP: previous period]

Where am I going wrong?

Many thanks in advance,

Luca

TristanS

@LucaRicci try this formula for the cumulative line item

'layers - actual' + previous('layers - cumulative')

This will keep accumulating though from beginning to the end of the time period. So if you need to reset at some point you will need to add a condition. E.g.

If <condition to reset> then

'layers - actual'

else

'layers - actual' + previous('layers - cumulative')

LucaRicci

LucaRicci

@TristanS unfortunately, I cannot use that because my line items do not have a timescale. Might CUMULATE be worth a try? so far I have not been able to make it work though..

SriNitya

LucaRicci CUMULATE adds values and returns a number. By default, the values are added across a time period. You can also choose to add values across a list.

https://community.anaplan.com/discussion/80762/2-02-10-using-previous-vs-cumulate

LucaRicci

@SriNitya seems like I can't use cumulate due to my week list being a flat list

TristanS

@LucaRicci so week 1 to week 3 in your screenshot is a list rather than time period. One thing you can do is map that week list in to a time period and execute the cumulate against the time period then remap it back to your week list.

E.g in time period module

Add Line item weeklist with formula

Time period week 1 maps to week 1 list item

Time period week 2 maps to week 2 list item

Add lineitem layersactual with formula

Sourcemodule. Layers actual[lookup: weeklist]
then you can apply the previous function as per earlier recoomendation

you can then push the result of the cumulate back to the original module using a lookup to the time period equivalent

SriNitya

@LucaRicci
Try this workaround  Cumulate adds values across a list.
Create a Mapping module for Weeks and then by using cumulate function you can desired output.
W1 is Week 1 True boolean

Let me know if it helps.