on 12-23-2014 6:53 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
Hi,
You can maintain a separate property in time to get this value to make this calculation dynamic.
Hope it helps..
regards,
Raju
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
13 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.