2.3.9.3 Update Historic Volumes for Baseline Forecast

For this activity my formula is not returning any value and I'm not sure where I'm going wrong.

IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN OFFSET(Volumes, -52, 0) ELSE 0

this is my formula that I've used but I'm not getting any results.

Accepted answers

@JosephPhilp634 If there is any previous year data then we will take data from last year else we should take data from last years forecast

you can use this formula

IF NOT 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN LAG(Default Forecast, 52, 0, STRICT) ELSE 'DAT03 Historic Volumes'.'Offset Volumes for 1st Forecast Year'

Screenshot 2023-06-05 5.35.20 PM.png

Screenshot 2023-06-05 5.35.56 PM.png

All comments

AkhilEmmanual

@JosephPhilp634 If there is any previous year data then we will take data from last year else we should take data from last years forecast

you can use this formula

IF NOT 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN LAG(Default Forecast, 52, 0, STRICT) ELSE 'DAT03 Historic Volumes'.'Offset Volumes for 1st Forecast Year'

Screenshot 2023-06-05 5.35.20 PM.png

Screenshot 2023-06-05 5.35.56 PM.png

JosephPhilp634

Thanks very much, i understand what i've done now

PreetiJain

@AkhilEmmanual

Hi, I have used the below formula, however m getting an alert as "circular references" as well as you may notice that the module name "DAT 03 Historic Volumes" is tuned into italics with a black dot next to it… please advise what am I doing wrong here..

also while the formula is running, it is NOT getting saved in formula bar against "offset volumes for 1st forecast year" line item..

Appreciate your help…

phi

@PreetiJain

"Purpose: If it is the first forecast year, then offset the values from the volumes 52 weeks earlier, else 0"

This is the formula I used which populates FY19 offset volumes with zeros and FY20 offset volumes with the volumes from 52 weeks before.

IF

'SYS01 Time Settings By Week'.'1st Forecast Year?'

THEN

OFFSET(Volumes, -52, 0)

ELSE

0

I also set the offset volumes line item Summary to Sum, as it seems a meaningful number and numbers are visible even when the page selector is not drilled all the way down to SKU level.

________________________________________________________________________

apologies for the repeated messages. They were disappearing on me on the day, so I kept trying again. A few days later and they're all there! It doesn't seem I can delete them, so I've just blanked the repeats

phi

JosephPhilp634

Hi,

I don't know if you have already solved this issue but if not here is some help.

So the reason it is in italics is because of the circular reference Anaplan wants you to fix the formula so it's put you back into the formula edit box for you to change the formula.

The cause for the circular reference here is the else statement, because this formula is saying if it is 'SYS01 Time Settings By Week'.'1st Forecast Year?' then take the data in 'DATA03 Historic Volumes'.'Offset Volumes for 1st Forecast Year' but this line item is not a manual input so there is no data to take. For this one because the line item is asking for the '1st forecast year' if it is not FY20 then it should 0

The formula I used, I did it as an 'if' statement rather than an 'if not' because the line item is '1st forecast year' so it is only wanting to fill the data for FY20 which is the first forecast year of the time settings.

I hope the problem has been resolved by now but if not this should hopefully help.

phi

Quick Links

Can you help?

Unanswered questions

Azure SQL Connection, database name with Hyphens in Data Orchestrator
I am attempting to connect to an azure SQL database vis at the MSSQL connector in data orchestrator but am having issues and I believe it is due to hyphens in database name (which is a fairly standard naming convention I will note!) Example below (fake credentials) - has anyone else encountered similar and is there a way…
Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…