Need Help: Circular Reference Error

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 Final Revenue

Accepted answers

All comments

Misbah

@jimfeng5

Instead of OFFSET can you try LAG(Final Revenue,y,z) and see if that works. Usually when there is a circular reference with OFFSET we use LAG.

Hope that helps

Misbah

andrewtye

Hi @jimfeng5

Couple of things:

1. Do you need the default revenue line? It might be easier to just reference the PY revenue line directly.

2. Can also put the "-52" element into the SYS module then you wouldn't need to have the IF... then... else... in your Option #2

As to lookup & ciruclar reference- i'm not sure what the answer is but to make lookup work to pull forward i've needed to break the formula linkage but offset gets around it. There's an article somewhere about it and will see if I can dig it up!

Andrew

jimfeng5

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

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.

ChrisAHeathcote

@jimfeng5

To account the change in the number of weeks in a year use a time systems module to count the number of weeks. Add a new line item formatted by year and use parent(item(time)) to return the year for each month. Then finally add a third line item called number of months formatted as number. Reference the first line item and use LOOKUP to reference the full year value so that you return the number of weeks in the year for each week of the year.

Use this in your LAG or OFFSET function.

Quick Links

Can you help?

Unanswered questions

Sorting General List
Hi, I wonder if there's a way I can sort my general list alphabetically. The list is used as a drop down menu in a line item module and I want the drop down menu to be easy to navigate. I've already tried the Order List action. I already have a module with a text-formatted line item based on that list. But when I try to…
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.…