Accepted Solutions (1)
Answers (2)
Hi Vadim
Ex: Time selection 2009.06 ( Forecast is calculating only from Sept to Dec only not not for Budget and Plan.
and Taking values 1000+2000+3000/4) but our calculation is required 1000+2000+3000/3 and posting should be start from 2009.07 to 2010.03 ) + 1 yr Budget+4 years Plan month wise
Thanks
Kishore V
vadim.kalinin
Yes.Thank you and I got the difference. I have added together your previous code and current code and tested.
Fortunately it working as per my requirement. but only to fix small issue. i.e.
If user selection 2015.03>>> Forecast value is calculating positing in 2015.03 but not copying to Budget and Plan only problem in March month . I know that we have kept the select statement not to calculate in March. But need help to correct the code.
Script Logic:
*SELECT(%NOTMAR%,ID,TIME,ID=%TIME_SET% AND MONTHNUM<>12) //%NOTMAR% - empty for MAR *SELECT(%NOTFEB%,ID,TIME,ID=%TIME_SET% AND MONTHNUM<>11) //%NOTFEB% - empty for FEB
*SELECT(%TID%,TIMEID,TIME,ID = %TIME_SET%) *SELECT(%Y%,YEAR,TIME,ID = %TIME_SET%) *SELECT(%PREV%,ID,TIME,TIMEID<%TID% AND YEAR=%Y% AND CALC=N) //Prev periods *SELECT(%NEXT%,ID,TIME,TIMEID>=%TID% AND YEAR=%Y% AND CALC=N) //Next periods *SELECT(%LTID%,TIMEID,TIME,TIMEID<%TID% AND YEAR=%Y% AND CALC=N) //Prev TID's *SELECT(%NUM%,MONTHNUM,TIME,TIMEID>=%LTID% AND TIMEID<%TID% AND CALC=N) //Number *SELECT(%TIDG%,TIMEID,TIME,TIMEID>%TID% AND YEAR=%Y% AND CALC=N) //Next TID's *SELECT(%NEXTMONTH%,ID,TIME,TIMEID>%TID% AND TIMEID<=%TIDG% AND YEAR=%Y% AND CALC=N) //Next Month *SELECT(%NTID%,TIMEID,TIME,ID=%NEXTMONTH%)
// *SELECT(%NEXT%,ID,TIME,TIMEID>%NTID% AND YEAR=%Y% AND CALC=N)
//Next periods current year *SELECT(%TIDMAR1%,TIMEID,TIME,MONTHNUM=12 AND YEAR=%Y% AND CALC=N) *SELECT(%TIDG1%,TIMEID,TIME,TIMEID>%TIDMAR1% AND CALC=N) //Next TID's *SELECT(%Y1%,YEAR,TIME,TIMEID>%TIDMAR1% AND TIMEID<=%TIDG1% AND CALC=N) *SELECT(%NEXT1%,ID,TIME,YEAR=%Y1% AND CALC=N) //Budget *SELECT(%TIDMAR2%,TIMEID,TIME,MONTHNUM=12 AND YEAR=%Y1% AND CALC=N)
*SELECT(%TIDG2%,TIMEID,TIME,TIMEID>%TIDMAR2% AND CALC=N) //Next TID's *SELECT(%Y2%,YEAR,TIME,TIMEID>%TIDMAR2% AND TIMEID<=%TIDG2% AND CALC=N) *SELECT(%NEXT2%,ID,TIME,YEAR=%Y2% AND CALC=N) //Plan1 *SELECT(%TIDMAR3%,TIMEID,TIME,MONTHNUM=12 AND YEAR=%Y2% AND CALC=N)
*SELECT(%TIDG3%,TIMEID,TIME,TIMEID>%TIDMAR3% AND CALC=N) //Next TID's *SELECT(%Y3%,YEAR,TIME,TIMEID>%TIDMAR3% AND TIMEID<=%TIDG3% AND CALC=N) *SELECT(%NEXT3%,ID,TIME,YEAR=%Y3% AND CALC=N) //Plan2 *SELECT(%TIDMAR4%,TIMEID,TIME,MONTHNUM=12 AND YEAR=%Y3% AND CALC=N)
*SELECT(%TIDG4%,TIMEID,TIME,TIMEID>%TIDMAR4% AND CALC=N) //Next TID's *SELECT(%Y4%,YEAR,TIME,TIMEID>%TIDMAR4% AND TIMEID<=%TIDG4% AND CALC=N) *SELECT(%NEXT4%,ID,TIME,YEAR=%Y4% AND CALC=N) //Plan3 *SELECT(%TIDMAR5%,TIMEID,TIME,MONTHNUM=12 AND YEAR=%Y4% AND CALC=N)
*SELECT(%TIDG5%,TIMEID,TIME,TIMEID>%TIDMAR5% AND CALC=N) //Next TID's *SELECT(%Y5%,YEAR,TIME,TIMEID>%TIDMAR5% AND TIMEID<=%TIDG5% AND CALC=N) *SELECT(%NEXT5%,ID,TIME,YEAR=%Y5% AND CALC=N)
//Plan4 //Run script only if user selection is not MAR *FOR %TMAR%=%NOTMAR%
//Store average of %PREV% in %NEXTMONTH%
*RUNALLOCATION
*FACTOR=1/%NUM%
*DIM TIME WHAT=%PREV%; WHERE=%NEXT%
*DIM CATEGORY WHAT=Actual; WHERE=FORECAST
*DIM RPTCURRENCY WHAT=LC;WHERE=LC
*ENDALLOCATION
//Run allocation only if user selection is not FEB
*FOR %TFEB%=%NOTFEB%
//Copy %NEXTMONTH% to %NEXT% for FORECAST
*RUNALLOCATION
*FACTOR=1
*DIM TIME WHAT=%NEXTMONTH%; WHERE=%NEXT%
*DIM CATEGORY WHAT=FORECAST; WHERE=FORECAST
*DIM RPTCURRENCY WHAT=LC;WHERE=LC
*ENDALLOCATION
*NEXT //Copy %NEXTMONTH% to %NEXT1% from FORECAST to Budget
*RUNALLOCATION
*FACTOR=1 *DIM TIME WHAT=%NEXTMONTH%; WHERE=%NEXT1%
*DIM CATEGORY WHAT=FORECAST; WHERE=BUDGET
*DIM RPTCURRENCY WHAT=LC;WHERE=LC
*ENDALLOCATION
//Copy %NEXTMONTH% to %NEXT2%,%NEXT3%,%NEXT4%,%NEXT5% from FORECAST to Plan
*RUNALLOCATION
*FACTOR=1 *DIM TIME WHAT=%NEXTMONTH%; WHERE=%NEXT2%,%NEXT3%,%NEXT4%,%NEXT5%
*DIM CATEGORY WHAT=FORECAST; WHERE=Plan
*DIM RPTCURRENCY WHAT=LC;WHERE=LC
*ENDALLOCATION
*NEXT
Thanks
Kishore V
Hi Vadim,
I have validated the script in UJKT and I am getting error Factor=1/ is not valid in default.
UJKT validation
Could you please him . Is any thing I have missed.
Thanks
Kishore V
Hi Vadim,
I have give the time selection 2015.05 in UJKT and in Input form after saving the data getting factor =1/not validated (Script is calling from Default for testing purpose later I will call through DM package)
Input form :
Logic is getting calculated if I am removing %NUM% in (factor =1/%NUM%) but only copying April month values to Forecast,Budget.
Thanks
Kishore V
vadim.kalinin
Hi Vadim,
Yes, user selection is the last month of actual, not the first month of forecast and there is no change in requirement.
Results: Average of each month copy to Forecast,Budget ,Plan
2015.04: Actual,1000/1 month= >>> copy to Forecast ,Budget ,Plan
2015.05: Actual,1000+1000/2 month=>>> copy results to Forecast,Budget,Plan
2015.06: Actual 1000+1000+1000/3 month =>>> copy results to Forecast,Budget,Plan
Many thanks for your help for code.
Kishore V
vadim.kalinin
Thank you Vadim,
what do you want to have for 2016.03?
Full year Actual display No forecast on March
Example
Results Avg Actual from APR to MAR copy to BUDGET 1 year and Plan 4 years montly =12000/12 >>>>>>> copy to BUDGET AND PLANCan you adjust your previous code and help me.
Thanks
Kishore V
Bluesky| User | Count |
|---|---|
| 41 | |
| 4 | |
| 4 | |
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.