2.3.9.2 Update Historic Volumes for Baseline Forecast

ankitaga2000

Hi

 

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

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

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

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?

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.