Hi,
What is the best practice way to do a classic brought forward / movements / carried forward calculation? Using OFFSET in the brought forward formula to capture the previous period? Or is there a better way?
TIA
@GaryPage ,
You can use either OFFSET or Previous() where I believe Previous() performs better.
Thanks,
Rob
@rob_marshall
Does PREVIOUS perform better than OFFSET as well? My understanding was that PREVIOUS is only better than CUMULATE...
@GaryPage
Previous is the way that you described the problem to it makes the formula easier to understand
Here's a pro-tip to avoid having an extra line to hold the Opening Balance
1. Create a module that doesn't have time to contain the Opening Balances if you can
2. In a Time Settings module, calculate the first period and the other periods (and the last one if you need to)
3. Now create the Opening / Closing calculation. Note it is more efficient to have the most common condition first
I've also set the time summary options for Opening and Closing to Opening Balance and Closing Balance respectively
I hope this helps
David
I did not consider setting up the 1st opening balance in my answer.
@DavidSmiths solution uses a combination of OFFSET and PREVIOUS.
OFFSET(1,-1,0) = 0 has a very specific purpose here. It is determining whether that line item has any periods preceding it -
( the 1 refers to period 1 which is the start of the offset and the -1 the period immediately prior to period 1 which is the destination. It is attempting to retrieve the value of the cell 1 period in the past from the current period. If this is not possible a zero will be returned. As this is impossible for the 1st period then zero is the answer which as the whole formula equals zero will return a true result - a tick in the box ).
This then sets up the conditions possible to populate the first opening balance with PREVIOUS being used to pull these balance forward to the next period.
You can use the native 'brought-forward' functionality of Anaplan with 'PREVIOUS' formula.
Checking the 'brought-forward' Boolean in blueprint view, will activate the functionality and a new time period will get auto-added which holds your opening balance.
In the screenshot attached, 'Dec 17' will auto- create when you check 'brought-forward' in blueprint. However, your actual time starts from 'Jan 18'.
@DavidSmith This will minimise your workaround effort 😉
Regards,
Pavan
@PavanKumar
That was my first thought but the very first opening balance needs to be established which requires someway of determining where the time period range starts.
OFFSET(1,-1,0)=0 determines the existence of a time period immediately prior to the opening balance. if the time period is the the first in the range then a zero will be returned and the formula will resolve to a TRUE boolean result if it is not then the formula will resolve to FALSE.
An alternative to OFFSET is PREVIOUS('1')=0
'1' is an additional line item
It's a good point, but the challenge we've see with Brought forward is that the time period doesn't actually exist! It's hard to map data in and out of it, so we have tended to avoid using it. But in some cases it works, so thanks for mentioning it as an option
As with a lot things in Anaplan there are many ways to achieve the same result - The challenge, and what we've articulated with PLANS is the Best practices and when to use one technique over another
Hi, I have a worksheet for end users to enter new hires into, for which the list item which persist across versions. I am having an issue where the end user can delete a list item using the grid functionality because the Current Version value is blank, but I want to prevent them deleting them (as data will be lost from…
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
