Getting Sum of Line Item's Previous Values without Time Dimension
Hi Everyone,
I'm trying to find a way for the following challenge:
1. There is a standard business case model for a supermarket that includes such lines as CAPEX, Revenue, Expenses, etc. The standard business case model doesn't have a specific launch month or data of launch for a supermarket as this is a business case for a TYPICAL supermarket. This means that this business case model doesn't have specific months like Jan 21, Feb 21, etc. but rather Month 1, Month 2, etc.
2. In this model there are several financial metrics that require to use previous values of same LI that is calculated. For example, to calculate cumulative FCF to find the breakeven point
Below you can find the current realization which I've made but now I'm struggling with the problem of not having Time dimension.
Can anyone suggest a workaround while we all wait for the implementation of this idea? https://community.anaplan.com/t5/Idea-Exchange/PREVIOUSITEM-formula-which-is-like-PREVIOUS-but-references-a/idi-p/46481
Thank you in advance,
Stanislav
Answers
-
Could you create a mapping module that relates your fake monthly calendar to the list item that should be looked up for previous? Then you can use the LOOKUP function. If you need to go back further than one month you can just cumulate the values.
So in your example for month "2" you would first obtain the previous month "1" from your mapping module. Then lookup the LI value from month "1".
Just an idea.
1 -
Have you explored the CUMULATE function in Anaplan?
https://help.anaplan.com/en/005a5a59-bb08-45e1-a4b2-6e658bad9832-CUMULATE
If you are trying to keep a running total of everything in that line item then this might help you achieve it. You can natively cumulate across time, but there is an optional input in the syntax which lets you cumulate over a list such as fake time.
Hope this helps!
Jason1 -
@jasonblinn , hi.
Thank you for your reply. Quick one: is there a way to use it somehow with an offset of -1 on the list, like previous?
I'm asking because without offset - I get circular reference error
0 -
Thank you for your reply. I saw this kind of proposal somewhere else, but, unfortunately, can't perfectly understand this from a technical perspective.
If I understand correctly what is proposed is that:
1. Create a new module with time range
2. In that new module I add LI where I manually map each month to 1-60 # of Months that I have right now in my current module
3. In that new module I reproduce all LIs and formulas that I have in my current module and I map data from my current module to the new module based on additional LI representing my 1-60 # of Months
4. After I get data this way in the new module, I can perform the required calculations
5. After performing all calculations, based on additional LI - I return data back
Did I get it right? Or my understanding is incorrect?
0 -
Having thought about this a little more, I'm wondering if it's possible to just use a normal calendar. The years would obviously be arbitrary (doesn't really matter) since all you need is 60 periods. Then designate one of those months as the "month 0" by creating a system module using the time range you created. Now you can use any time function you want, including CUMULATE, as @jasonblinn suggests. You won't get any circular references either.
0 -
I was looking into a non-calendar solution because further in other modules there will be a forecast for the launch of supermarkets wherein, for example, each month there will be a launch of specific amounts of supermarkets and each time there is a new supermarket coming, forecast from the non-calendar module will kick in (see example below). How will I arrange that if, for example, I will change the non-calendar module to the calendar with start in Jan 2020 while new supermarkets will appear only, for example, in Jul 2021 or even 2022?
+ When in near future I will have to change main model year, I will loose Jan 2020 and there is a chance that my standard supermarket module can break
WDYT?
0 -
I like where you're going with this but there may be some challenges later as you noted with using a time range. As long as it's not to much of a burden to map your fake calendar to a time range, then okay. My thought was to just create a system module that uses your fake calendar and add a line item that is formatted with your fake calendar that you will use for mapping. For example, if "15" is month zero, then you would assign it "14" for the look back one month. "14" would get "13", etc. "1" would either be assigned itself or blank. In your planning module you'll do a lookup on each of your fake calendar values and then use Booleans to designate the time range you want. Then another line item that only includes values in your time range.
Anyway, something like that.
0 -
@JaredDolich , I'm trying my best but looks like I don't have enough experience to understand your following proposal:
"For example, if "15" is month zero, then you would assign it "14" for the look back one month. "14" would get "13", etc. "1" would either be assigned itself or blank. In your planning module you'll do a lookup on each of your fake calendar values and then use Booleans to designate the time range you want. Then another line item that only includes values in your time range."
Looks like I have to discuss it with our Subcontractor
Meanwhile, I'm thinking that maybe something like this can work but I have to dig deeper and redo my standard module completely...
1
