Need Help: Circular Reference Error

Options
jimfeng5
jimfeng5 Member, ALL USERS, GroupMember, Certified Master Anaplanner, Community Member, Certified Model Builder Posts: 5 Certified Master Anaplanner
in Planning & Modeling

Hi Anaplan Community,

I have been thinking of ways to pull Prior Year value into Current Year. Previously, I used OFFSET() formular where I would offset -52 weeks. I have created a demo module below to showcase the original formula, the formula that give me circular reference error and the backup formula.

Formula with circular reference.JPG

Recently, to adapt to 2023 having an additional week (53 weeks instead of 52 weeks), I wanted to get rid of offset (-52 weeks) formulas and decided to create the time system module below and then use it to lookup the Prior Year's weeks.

System Module to produce PY Weeks.JPG

 

However, when I tried using the formula in the PY Revenue line = "Final Revenue[LOOKUP: 'SYS00 Time Setting Current Period'.'Prior Year Week List (Native)']", it's giving me a 'circular reference' error. I don't understand why OFFSET formula worked but my lookup PY Weeks formula does not. Can someone please help me understand this? Does anyone have any other elegant designs?

 

Additional Screenshots:

Formula for Default RevenueFormula for Default RevenueFormula for Final RevenueFormula for Final Revenue

Tagged:

Comments

  • jimfeng5
    jimfeng5 Member, ALL USERS, GroupMember, Certified Master Anaplanner, Community Member, Certified Model Builder Posts: 5 Certified Master Anaplanner
    Hi Misbah,
    I have no issue with using OFFSET, it's when I tried to use the formula "Final Revenue[LOOKUP: 'SYS00 Time Setting Current Period'.'Prior Year Week List (Native)']" and then it's given me a circular reference error.
  • jimfeng5
    jimfeng5 Member, ALL USERS, GroupMember, Certified Master Anaplanner, Community Member, Certified Model Builder Posts: 5 Certified Master Anaplanner
    Morning Andrew,
    1. Yes I do need the default line, it helps the user to determine the starting point of their forecast. The example I provided is overly simplified of what we actually have in our model. I just stripped all the unnecessary information hoping to make my issue stand out.
    2. good call!
    Please let me know when you find the article.

Categories