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 · Accepted solution]

@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

[jasonfortunato]

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 zeroWhy? 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

[jasonfortunato]

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

[jasonfortunato]

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.

[jasonfortunato]

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.