## 2.3.9.2 Update Historic Volumes for Baseline Forecast

Frequent Contributor

## 2.3.9.2 Update Historic Volumes for Baseline Forecast

Hi

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.

Regards,

Ankita

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

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
9 REPLIES 9
Moderator

## Re: 2.3.9.2 Update Historic Volumes for Baseline Forecast

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
Frequent Contributor

Thanks a lot

Regards,
Ankita
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"?

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?

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 🙂

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.

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.

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.

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.