Register

Level 2 Sprint 2 - 2.3.11.3 Create Line Chart Saved View

pmorgan
Contributor

Level 2 Sprint 2 - 2.3.11.3 Create Line Chart Saved View

Hi there,

 

While creating the saved view for the line chart for my DEM03 Demand Forecast module i noticed that I only have 1 year of data for FY20.  The requirement is asking for a view with 2 years of data (FY20 & FY21).   I'm looking for some help in getting my formula correct.

 

What i did was i created a new line item in my DAT03 Historic Volumes to give me the volume for FY21.  Like this:

 

Offset Volumes for 2nd Forecast Year: 

 

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

 

(I simply removed the negative sign that was in front of the offset parameter for the 1st Year line item.)

 

Then in my DEM03 Demand Forecast module I entered this formula to give me the 2 year view:

 

IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN 'DAT03 Historic Volumes'.'Offset Volumes for 1st Forecast Year' ELSE 'DAT03 Historic Volumes'.'Offset Volumes for 2nd Forecast Year'

 

I did not get an error, but i also did not get values in FY 21.  Here's what my view looks like with the formula in there:

 

no values in fy21.png

 

Can anyone help guide me in the right direction?

 

Many thanks,

 

-Paul 

 

 

3 REPLIES 3
Puneeth H P
Frequent Contributor

Re: Level 2 Sprint 2 - 2.3.11.3 Create Line Chart Saved View

Hi @pmorgan ,

 

For the '1st Forecast Year' Here you can select '2nd Forecast year' as just 'NOT 1st Forecast year' . It will be selected FY21 . 

 

Then for the historic volumes you can use nested If condition . 

 

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

You can take values OFFSET(Volumes,-52,0) only for the '2nd Forecast Year' as well. 

 

Correct me if I'm wrong . 

 

Hope this helps . Let me  know weather this works or not . 

 

Thank you,

Puneeth HP 

 

 

pmorgan
Contributor

Re: Level 2 Sprint 2 - 2.3.11.3 Create Line Chart Saved View

@Puneeth H P 

 

Thanks for your help.  I also ended up getting some help on this one from a colleague of mine.  He suggested I utilize the Default Forecast line item, which makes perfect sense.  I don't know why I didn't think of this initially.  So this resulted in using this formula which appears to be giving me the correct data in FY20 and also in FY21:

 

Baseline Forecast = 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)

 

This results in the following, where I have values in both FY20 & FY 21, and my FY21 values are from the Default Forecast line item :

 

working.png

 

In a line chart it looks like this:

 

line chart.png

So i'm going with this.

 

-Paul

 

kotoponus
Contributor

Re: Level 2 Sprint 2 - 2.3.11.3 Create Line Chart Saved View

@pmorgan 's post here has been very helpful indeed, but it leaves a feeling if you are doing the right thing.  For anyone who wants an assurance that you are going for the right direction, here is the one.  In task 2.4.4.2, on the second bullet point, along with creating the overriding architecture for volume amounts, you are asked to check if you are using the default forecast values from FY20 for FY21.  So, this supports Paul's suggestion.  

Screenshot 2020-11-27 at 13.21.06.png