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
Comments
-
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)
0 -
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
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.
0 -
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
0 -
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?)
1 -
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
0 -
Also, make sure the summary of Current Period? is set to "ANY"
0 -
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
0 -
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.
1 -
Hi Jared,
It finally worked! Thanks so much.
I did as you showed. Not sure if it had to do with a filter which I had on as I was trying to turn off monthly summaries. With the filter off that seems to have done the trick - does that make sense?
Again thanks so much for persevering with me through this step. I'm sure there are a lot more challenges ahead but I'll take it a step at a time.
Level 2 is a lot tougher than Level 1 but is through instances like this where the learning really starts to sink in.
People like you really make a difference to the learning process!
A big thanks again Jared.
Kind regards,
Clarence
1 -
Haha! Well, now you know the answer for REV03!!
You got this. Can't wait to see that L2 Certification!!
0 -
Not there yet Jared although it'll be Back to the Future when I arrive at Rev03 LOL.
1 -
Thanks to your conversation, I've got what I was looking for.
Thank you for popping up the question @ClarenceAndre and Thank for answering @JaredDolich 😉
0
