How do I select a prior list item without getting a circular reference

I am trying to build a model that looks like the above screen shot. Tier 1 - Tier 5 are list items and everything in the top row are list items. I am having trouble getting any ending balance to work (for reference, I'm able to begin the tier 2 starting balance and so on and so forth to pull the ending balance from the prior tier) but I can't put a simple subtraction formula in ending balance without getting a circular reference. I've tried adding a mapping column called previous tier that is formatted by the tier list and doing a lookup but that is giving me the same issue.

Accepted answers

All comments

Dikshant

I tried to get the values that are in your Excel. We can make Map Tier line item as formula driven if the pattern of fetching values is constant between Starting Balance and Ending Balance.

wld002

My starting balances aren't be input driven, the tier 1 starting balance is coming from a different module, the ending balance would also be formulaic (starting balance - tier 1 amount) and then starting balance for each additional tier would be the ending balance from the prior tier.

wld002

I've tried it with the mapping tiers this way as well and I still can't avoid a circular reference

Dikshant

Are you referring ending balance in starting balance formula directly or indirectly?

wld002

directly. if my tier 1 ending balance is $20,000 then I need that to be my tier 2 starting balance. My starting balance formula is if item(tiers) = tier 1 then spend (from another module) else prior tier ending balance. My ending balance formula is starting balance minus amount for calc

Dikshant

Is it possible to share formulas or screenshots of modules for better understanding?

PujithaB

Hi @wld002
Very Interesting and realistic Question
So, As per the above discussion I have to understood that opening balance = previous (Closing Balance) and Closing balance = Opening balance - Additional calc

As Formulas itself is telling the circular reference
To my knowledge there no direct way to achieve this
So, I have created an intermediate line item which stores opening balance by running an import and I have referred that intermediate line item in Closing balance.

This is one workaround through which we can achieve desired result.
Let me know if this helps or if you find out closing balance independent of opening balance then you will not get circular reference.

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385