2.3.9.2 Update Historic Volumes for Baseline Forecast

ankitaga2000

I am having trouble with the formula creation ,Please help

I am using the below formula :

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

I am getting an error as invalid formula.

please help.

Regards,

Ankita

Comments

JaredDolich

@ankitaga2000

You're super close. You just added a little to much. Rarely would Anaplan set you up to create a nested IF statement. Anytime you encounter a situation where you feel you need to add multiple IF statements in the same formula, try to break it up into separate line items. However, in this case you can handle with one conditional.

Here's the correct formula:

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

Here's yours

ankitaga2000

Thanks a lot

Regards,
Ankita

jadefortunato

Why are we using a negative offset (-52)? Don't we want to push the FY19 data forward 52 weeks?

When changing my formula to incorporate the -52 vs 52, it did not throw an error, but also didn't populate FY20:

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

However the version you posted, which omitted the "= TRUE" worked. Why is this happening? I was told that Anaplan defaults to an IF/THEN/ELSE format when no operators are entered, but I could not make the formula work without an "IF"... why does adding the condition "= TRUE" throw it? What if I wanted "= FALSE"?

benkoz75

Hi Jared,

I used same formula but no calculation happen. All zero 😞 Why? Am I missing something?

2_119329.jpg

benkoz75

I think system need to restart or something like that because after one day my formula start working and I didn't do anything just waited one day 🙂

jadefortunato

Mine are actually also zeros now. I don't know what's going on.

jadefortunato

Hi Jared? This doesn't seem to result in anything happening. 2019 remains 0, and 2020 is blank.

Kirkpatrick

If you did prior steps properly - Try this:

1 - Ensure you refresh your screen so the calculations come through.

2 - Fix your pivot so the "Line Items" follows AFTER the P3 SKU. Doing it before makes it look like it is missing because it is putting those all to the bottom.

jadefortunato

So this is going to sound odd, but when I went to show this to the instructor there were values even though I didn't change anything in the module.

Habi786

can you show final output of FY20

Quick Links

Recent release news

May 2025 platform releases and what’s next
Check out the latest Anaplan feature updates and enhancements in our May 2025 official release notes. The information below offers supplemental information to that post. Integrations and extensibility Preview dataset when mapping to model or selecting for transformation view in ADO When selecting a dataset to map to a…
April 2025 platform releases and what’s next
Check out the latest Anaplan feature updates and enhancements in our April 2025 official release notes. The information below offers supplemental information to that post. UX New UX for Models section in Administration The Models section in Administration has been redesigned with a more intuitive interface. Tenant…