## help with offset formula

Regular Contributor

## help with offset formula

Hi I'm going through L2MB training and trying to figure out how to work this formula: IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN OFFSET(Volumes, -52, 0) ELSE 0

I think the first part of the formula is correct but I'm not sure if the second part within OFFSET(...) is. Volumes is where the historical data is say in FY19 and I'm trying to populate FY20 (1st Forecast Year) with data from 52 weeks ago hence the -52 (weeks).

The formula is returning me 0s which shouldn't be the case as FY19 is populated while the '1st Forecast Year?' boolean is TRUE.

Is it apparent where the error is?

Clarence

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

Ok! We're getting closer. Let's check one more thing. Change your selectors to the lowest level, SKU and Account.

If the summaries are turned off then you won't see anything at a higher level, only the lowest.

Jared Dolich
13 REPLIES 13
Moderator

So close. Your on the right track. The IF statement is trying to determine if this this is the first forecast year or an elapsed year. If it is you need to bring the history forward. If it's the second year, you need the forecast from 1st year.

Your Formula.

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

Correct Formula

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

Jared Dolich
Regular Contributor

Thanks for your quick response Jared.

Couple of related questions:

1. Do we need explicit reference to DAT03 if the formula is in the same module?

2. Why do we need ELSE OFFSET(Default Forecast, -52, 0)? If '1st Forecast Year?' is 0 wouldn't it just be ELSE 0 instead of the longer expression?

Kind regards,

Clarence

Moderator

That's a good question. Let's break down the goal.

• If I'm in the first year, I need to move history forward as a starting point for volume.
• If I'm in the second year, I need to move the 1st year's forecast forward as a starting point for volume.

To answer the first point, you have to reference the history module. Now, the history module uses the same model calendar, so in order to see the history in the first year it also has to be moved up a year. That's why we reference DAT03.

To answer the second point, you have to reference your forecast from the first year, or your default forecast.

If you have an "ELSE 0" that wouldn't make sense because you don't want a zero. You want a baseline forecast for 2020 and 2021.

Jared Dolich
Regular Contributor

Hi Jared,

Thanks so much again for your additional pointers. I've been thinking hard about what you explained but am not getting it.

Perhaps some additional details might help.

The formula I'm trying to set up is in DAT03 and the relevant line item, Volumes is within the same module. In the formula construct, I point towards Volume and DAT03 doesn't show up because its in the same module. Do I still need an explicit reference to DAT03?

Additionally, the time range is FY19-20. FY19 is populated with historical/actuals while FY20 are all zeroes. The task is to pull over the FY19 figures into FY20 (currently all blank) to serve as the baseline for the next step in the model build.

Here is where I'm stuck - when I'm in FY20 I should be satisfying the '1st Forecast Year' boolean and therefore look up the relevant week from 52 weeks ago (hence the -52 offset) and if that condition is not satisfied, it should be a simple null (0). In fact the situation shouldn't arise as there are only 2 years - FY19 and FY20.

Jared, I'm not getting the part about moving history forward unless you meant moving FY19 a year forward? Are you suggesting I should be using a +52 instead of -52?

Kind regards,

Clarence

Moderator

Gotcha. You'll get to deal with this again when you build REV03.

For DAT03 the correct formula is:

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

So your formula is correct. So, if you're getting zeros in 2020 then you need to check SYS01.

Make sure your formula for 1st Forecast Year is: YEARVALUE(Current Period?)

Jared Dolich
Regular Contributor

Thanks Jared, that looks a lot more like it ... let me check that out in the morning in my neck of the woods.

Again thanks so much for all your pointers!

Kind regards,

Clarence

Moderator

Also, make sure the summary of Current Period? is set to "ANY"

Jared Dolich
Regular Contributor

Hi Jared,

I checked the following:

1. Formula you confirmed correct;

2. Time settings formula of YEARVALUE(Current Period?) also correct

3. Summary of Current Period? set to Any confirmed.

They seem to be all correct so not sure why am getting all zeroes for FY20.

Any other suggestions?

Kind regards,

Clarence

Moderator

Ok! We're getting closer. Let's check one more thing. Change your selectors to the lowest level, SKU and Account.

If the summaries are turned off then you won't see anything at a higher level, only the lowest.

Jared Dolich