2.3.9.2 Update Historic Volumes for Baseline Forecast

ankitaga2000
Frequent Contributor

2.3.9.2 Update Historic Volumes for Baseline Forecast

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

1 ACCEPTED SOLUTION

Accepted Solutions
JaredDolich
Moderator

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

@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


Jared Dolich

View solution in original post

9 REPLIES 9
JaredDolich
Moderator

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

@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


Jared Dolich

View solution in original post

ankitaga2000
Frequent Contributor

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

Thanks a lot

Regards,
Ankita
jasonfortunato
Contributor

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

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
Contributor

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

Hi Jared,

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

benkoz75
Contributor

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

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
Contributor

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

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

jasonfortunato
Contributor

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

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

Kirkpatrick
New Contributor

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

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
Contributor

Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

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.