2.3.9.3 Update Historic Volumes for Baseline Forecast

JosephPhilp634

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.

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

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

phi