Rolling Forecast script logic (Average of month's actual to be forecast for remaining month of current year)

[bhagyesh_ravange]

Hello Experts,

I have one requirement wherein Actual of month pertaining to Current year to be averaged and is to be posted as a Forecast for remaining months

For Example:

1) Fiscal Period is from April 2014 to March 2015

2) In the below mentioned example current period is 2014.06, hence actual data would be available from 2014.01 to 2014.05. I need to average Actual data from 2014.01 to 2014.05 and post the values to the period 2014.06 to 2014.12.

3) I would be selecting Time period from Data manager package.

The issue i am facing is I am not able to average-out actuals in script logic through providing variable. I have marked the same in red,in below script logic.

Script Logic:

*SELECT(%CURRENTMONTH%,"[LEVEL]",TCMOL_TIME,"[ID]='%TCMOL_TIME_SET%'")

*SELECT(%CURRENTYEAR%,"[YEAR]",TCMOL_TIME,"[ID]='%TCMOL_TIME_SET%'")

*SELECT(%ACTUALMONTHS%,"[ID]",TCMOL_TIME,"[ID]<'%TCMOL_TIME_SET%' AND [YEAR]='%CURRENTYEAR%' AND [LEVEL]='%CURRENTMONTH%'")

*SELECT(%FORECASTMONTHS%,"[ID]",TCMOL_TIME,"[ID]>'%TCMOL_TIME_SET%' AND [YEAR]='%CURRENTYEAR%' AND [LEVEL]='%CURRENTMONTH%'")

*FOR %TT% = %FORECASTMONTHS%

*XDIM_MEMBERSET TCMOL_TIME = %ACTUALMONTHS%

*WHEN TCMOL_TIME

*IS*

*REC(EXPRESSION = %VALUE% / 6,TCMOL_TIME = %TT%)

*ENDWHEN

*NEXT

Regards

Bhagyesh Ravange

[former_member186338 · Accepted solution]

Hi Bhagyesh,

"The issue i am facing is I am not able to average-out actuals in script logic through providing variable. I have marked the same in red,in below script logic." - but what is the issue? You get incorrect result or what?

The line: *REC(EXPRESSION = %VALUE%/6,TCMOL_TIME = %TT%) is OK

Vadim

[former_member186338 · Accepted solution]

May be %VALUE%/5? you have 5 months for actual...

[bhagyesh_ravange · Accepted solution]

Hello Vadim,

Thanks for your prompt reply,

Issue is i want to make the 5 or 6 whatever it might a variable depended upon which time period I have selected.

Suppose I select in DM 2014.06 then it should be %VALUE%/5, if I selected in DM 2014.07 then it should be %VALUE%/6. I want to make the dividing factor to be variable depended upon Time period I select in Data manager package

Regards

Bhagyesh Ravange

[former_member186338 · Accepted solution]

Use BASE_PERIOD property...

[former_member186338 · Accepted solution]

Script:

*SELECT(%BP%,"[BASE_PERIOD]",TCMOL_TIME,"[ID]=%TCMOL_TIME_SET%")

*SELECT(%Y%,"[YEAR]",TCMOL_TIME,"[ID]=%TCMOL_TIME_SET%")

*SELECT(%ACT%,"[ID]",TCMOL_TIME,"[YEAR]=%Y% AND [BASE_PERIOD]<%BP% AND [CALC]=N")

*SELECT(%FCS%,"[ID]",TCMOL_TIME,"[YEAR]=%Y% AND [BASE_PERIOD]>=%BP% AND [CALC]=N")

*XDIM_MEMBERSET TCMOL_TIME=%ACT%

*FOR %F%=%FCS%

*WHEN TCMOL_TIME

*IS *

*REC(EXPRESSION=%VALUE%/(%BP%-1),TCMOL_TIME=%F%)

*ENDWHEN

*NEXT

Vadim

[former_member186338 · Accepted solution]

Ups, sorry, not looked on your time dimension...

[former_member186338 · Accepted solution]

Correct code:

*SELECT(%M%,"[MONTHNUM]",TCMOL_TIME,"[ID]=%TCMOL_TIME_SET%")

*SELECT(%Y%,"[YEAR]",TCMOL_TIME,"[ID]=%TCMOL_TIME_SET%")

*SELECT(%ACT%,"[ID]",TCMOL_TIME,"[YEAR]=%Y% AND [ID]<%TCMOL_TIME_SET% AND [CALC]=N")

// For %TCMOL_TIME_SET%=YYYY.01 %ACT% will be empty and no REC will be executed!

*SELECT(%FCS%,"[ID]",TCMOL_TIME,"[YEAR]=%Y% AND [ID]>=%TCMOL_TIME_SET% AND [CALC]=N")

*XDIM_MEMBERSET TCMOL_TIME=%ACT%

*FOR %F%=%FCS%

*WHEN TCMOL_TIME

*IS*

*REC(EXPRESSION=%VALUE%/(%M%-1),TCMOL_TIME=%F%)

*ENDWHEN

*NEXT

[bhagyesh_ravange · Accepted solution]

Hello Vadim,

I getting "syntax error" while validating script logic.

Regards

Bhagyesh Ravange

[former_member186338 · Accepted solution]

First - use UJKT for testing!!!

Second - %FCS% is empty during validation, just save without validation!

Vadim

P.S. In UJKT provide month in Data Region and use EXECUTE(Simulate)

[bhagyesh_ravange · Accepted solution]

Hello Vadim,

Interestingly Script is not validating, but it is getting executed!!

Regards

Bhagyesh Ravange

[former_member186338 · Accepted solution]

There are a lot of scripts not validating but working fine. Scripts like this - without input parameter - the FOR operator is invalid!

You can use non validating scripts without any issues!

By the way, what is the reason to use LEVEL property???

Vadim

[former_member200327 · Accepted solution]

Hi Bhagyesh Ravange,

Since your WHEN and IS are not using %TT% you can move FOR/NEXT loop inside and have only REC in it. Just in case you care about such thing as performance.

Regards,

Gersh

[former_member186338 · Accepted solution]

Yes, FOR/NEXT inside WHEN/ENDWHEN will simply create required number of REC lines! And single WHEN/ENDWHEN will work faster then multiple... In this particular case with 1 to 11 loops the difference will be moderate...

Vadim

[bhagyesh_ravange · Accepted solution]

Hello Vadim,

Script logic is taking QUARTERS also into consideration that is why I have used LEVEL property to restrict the same to MONTHS.

Regards

Bhagyesh Ravange

[bhagyesh_ravange · Accepted solution]

Thanks Gersh, I have done the same..

[former_member186338 · Accepted solution]

Hi Bhagyesh,

There is no reason to get LEVEL property from the user selected month (from %TCMOL_TIME_SET%)!

You can simply put [LEVEL]=MONTH or [CALC]=N (if you have only months as base members).

Vadim

[bhagyesh_ravange · Accepted solution]

Thanks Vadim, i have done the same.

[former_member190501]

Hi,

You can maintain a separate property in time to get this value to make this calculation dynamic.

Hope it helps..

regards,

Raju

[bhagyesh_ravange]

Hello VaraPrasadraju,

Required property "Month" is already present in the Time Dimension, snap shot of which i have mentioned in the above discussion.

Is there a way to fetch that property value in the script logic.

Regards

Bhagyesh Ravange

[former_member190501]

Hi,

"Suppose I select in DM 2014.06 then it should be %VALUE%/5"

Your Month for 2014.06 is not 5 as per your attachment.  If you maintain a separate property as below then you can read this property in the script logic.

ID            Avg Mon

------------------------

2014.01  01

2014.02  01

2014.03  02

2014.04  03

You are using SELECT to get Year based on selected time period. Write one more select for new property.

*SELECT(%CURRENTYEAR%,"[YEAR]",TCMOL_TIME,"[ID]='%TCMOL_TIME_SET%'")

Thanks,

Raju

[former_member186338]

Hi Raju,

New property is not required in this case!

Vadim

[former_member190501]

Hi Vadim,

What will happen for first period if use BP - 1.

Thanks,

Raju

[former_member186338]

For the first period you can always use code like:

*WHEN TCMOL_TIME.PERIOD

*IS <> JAN

*REC(...

Just do nothing for the first period!

Vadim

The issue here is that the topic author is using different fiscal year... APR-MAR and BASE_PERIOD is for fiscal year... My code have to be adjusted

Vadim

[former_member186338]

Actually in this code it's not necessary to test for JAN etc...

If user enters 2014.01 the %ACT% selection will be empty - empty scope - no code execution

Vadim