Is it possible to override a formula in a line item? If not, how can I enter a starting amount?

I want to be able to enter a starting amount and from there on have a formula driven on the line item.

Example.

Beginning ARR manual enter amount in Apr, then it will drive the calculation to get the ending ARR. The beginning ARR will equal to previous months Ending ARR.

I included several screenshot. The excel file highlighted in yellow is the manual input amount. I was wondering whats the best way to approach this? I know I can just enter a line item to add to the total to make it work.

Screen_Shot_2019-07-01_at_5_48105.png

Accepted answers

kavinkumar

HI @kdoan ,

I have 2 thoughts on this.

Approach 1: If your time period starts with Apr (as you mentioned for the entry) then go with this approach. Create another input line item and make use of Offset formula.

Is it possible to override a formula in a line item.PNG

Approach 2 :

Is it possible to override a formula in a line item 1.PNG

Thanks,

Kavin.

All comments

kavinkumar

HI @kdoan ,

I have 2 thoughts on this.

Approach 1: If your time period starts with Apr (as you mentioned for the entry) then go with this approach. Create another input line item and make use of Offset formula.

Is it possible to override a formula in a line item.PNG

Approach 2 :

Is it possible to override a formula in a line item 1.PNG

Thanks,

Kavin.

DavidSmith

@kavinkumar @kdoan

I trick I use to avoid the extra line item for "opening balances" is to have a module (without time) to hold the opening balance or input

I then use my time settings module to highlight the first period of the timescale (as per below)

then the formula in the target module is:

IF Monthly Time Settings.First Period of timescale THEN Opening Balance.Data ELSE PREVIOUS(Closing Balance)

I hope this is useful

David

kdoan

@DavidSmith Thanks for your reply. I was wondering can you provide more details? I am new to this. I understand that you created a separate module (without time) to hold the opening balance or input.

However I got confuse by your screenshot below. I just see a check mark or boolean value so I am not so sure how you use separate module to the time setting module.

kdoan

@kavinkumar Your approach 2 works for me. Thank you. But your approach 1, with the offset formula doesn't work. I was wondering why?

Screen_Shot_2019-07-02_at_12_48155.png

rob_marshall

@kdoan ,

Not David but will respond for him. Essentially, he has a SYS Time module where he has a line item called "First Period of Timescale" formatted as a Boolean. That line item has a formula of OFFSET(1, -1,0)=0 which evaluates to true for the first period of the timescale. Once that is set up, your formula is just evaluating if the current time period checked in the SYS Time module (First Period of Timescale), then get the opening balance, else get the previous closing amount.

Hope this helps,

Rob

DavidSmith

@kdoan

Sorry for the confusion. What I didn't show was the "Opening Balance" module. This module would have the relevant dimensions but without timescale.

I hope this helps

David

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
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…