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.
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.
IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN OFFSET(Volumes, -52, 0) ELSE 0
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)
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.
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?