Accepted Solutions (0)
Answers (1)
Dear Vadim,
Thanks for letting me know the rules to ask a question related to script logic as it is my first time to post any question in the community so I wasn't aware of the existence of such rules.
On the other side telling me exactly what is incorrect in my logic will be much more helpful than just saying that it's incorrect specially that I already mentioned that the script is generating correct results and it has only the 2 problems mentioned above.
As requested below excel screenshot demonstrates the required calculation:
1. Our BPC Version & Support Package:
2.The K2 Calculation engine used is JAVESCRIPT
3.Sales Model consists of 8 dimensions :
Category
Channel (User Defined)
Input Currency
Location (User Defined)
Product (User Defined)
Profit Center (Entity)
Time
Vehicles Values(Account)
4.The Script is to be Launched by DM Package
5.Calculation Logic: Already Described in my 1st message
6. The script is shown below:
//TOTAL in channel and location are base level members containing the un-allocated figures
//ALL is a parent member grouping all sales channels (Retail, Wholesales...etc.)
//ALLHER is a parent member grouping all products of a certain type
*XDIM_MEMBERSET PRODUCT=BAS(ALLHER)
*SELECT(%TIMES%,[MONTHNUM],TIME,[YEAR]=$INPUT_YEAR$
*FOR %TIM%=%TIMES%
*RUNALLOCATION
*FACTOR=USING/TOTAL
*DIM CATEGORY WHAT=BUDGET; WHERE=BUDGET; USING=ACTUAL; TOTAL=<<<
*DIM LOCATION WHAT=TOTAL ;WHERE=<<<; USING=BAS(ALL);TOTAL=<<<
*DIM CHANNEL WHAT=TOTAL;WHERE=BAS(ALL);USING=<<<;TOTAL=<<<
*DIM TIME WHAT=$INPUT_YEAR$ ; WHERE=[YEAR]=$INPUT_YEAR AND [MONTHNUM]=%TIM% ; USING=[YEAR]="2016" AND [MONTHNUM]=%TIM% ; TOTAL=[YEAR]="2016"
*ENDALLOCATION
*NEXT
7.The user will input the target planning year (2017)
----------------------------------------------------------------------
What I mean by generating Correct results is that it generates correct results using 1 year not 3 years which means it's a correct logic script(From the technical point of view) but not satisfying all our requirements
2017 is a base level member and below is the structure/members of Time dimension:
Sorry, but you have incorrect structure of TIME dimension. All base members has to be in the 3 level structure (year-quarter-month). Like here 2015.INP is a base member for year input!
We created a dummy base level member 2017 to carry the total planned value and below is the complete time structure:
Below is the pasted script:
*XDIM_MEMBERSET PRODUCT = BAS(ALLHER)
*SELECT(%TIMES%,[MONTHNUM],TIME,[YEAR]=$INPUT_YEAR$)
*FOR %TIM% = %TIMES%
*RUNALLOCATION
*FACTOR = USING/TOTAL
*DIM CATEGORY WHAT=Budget; WHERE=Budget ; USING=Actual ; TOTAL = <<<
*DIM LOCATION WHAT=TOTAL ; WHERE=<<<
*DIM CHANNEL WHAT=TOTAL ; WHERE= bas(ALL) ; USING=<<
*DIM TIME WHAT=$INPUT_YEAR$ ; WHERE=[YEAR]="$INPUT_YEAR$" AND [MONTHNUM] = "%TIM%" ; USING= [YEAR]="2016" AND [MONTHNUM] = "%TIM%" ; TOTAL = [YEAR]="2016"
*ENDALLOCATION
*NEXT
That is exactly what I want to do
Also I have updated the Time dimension as shown below:
I've performed all the updates in the Time Dimension but the problem from the early beginning was that TMVL generates incorrect results when being used in RUNALLOCATION as shown below and as mentioned in my original message:
"Extra Comma is added to the member value"
Just tested the full script and got absolutely correct figures in the report:
*SELECT(%TID%,[TIMEID],PERIODS,[ID]=$INPUT_YEAR$)
*SELECT(%TIDSBEFORE%,[TIMEID],PERIODS,[TIMEID]<%TID% AND [YINP]=Y)
*SELECT(%TSAFTERINCL%,[ID],PERIODS,[TIMEID]>=%TIDSBEFORE% AND [YINP]=Y)
*SELECT(%PREVY%,[ID],PERIODS,[TIMEID]=%TIDSBEFORE% AND [ID]=%TSAFTERINCL%)
*SELECT(%TID1%,[TIMEID],PERIODS,[ID]=%PREVY%)
*SELECT(%TIDSBEFORE1%,[TIMEID],PERIODS,[TIMEID]<%TID1% AND [YINP]=Y)
*SELECT(%TSAFTERINCL1%,[ID],PERIODS,[TIMEID]>=%TIDSBEFORE1% AND [YINP]=Y)
*SELECT(%PREVPREVY%,[ID],PERIODS,[TIMEID]=%TIDSBEFORE1% AND [ID]=%TSAFTERINCL1%)
*SELECT(%TID2%,[TIMEID],PERIODS,[ID]=%PREVPREVY%)
*SELECT(%TIDSBEFORE2%,[TIMEID],PERIODS,[TIMEID]<%TID2% AND [YINP]=Y)
*SELECT(%TSAFTERINCL2%,[ID],PERIODS,[TIMEID]>=%TIDSBEFORE2% AND [YINP]=Y)
*SELECT(%PREVPREVPREVY%,[ID],PERIODS,[TIMEID]=%TIDSBEFORE2% AND [ID]=%TSAFTERINCL2%)
*FOR %MNTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
*XDIM_MEMBERSET TITLES=1110
*XDIM_MEMBERSET BE=BE1000
*XDIM_MEMBERSET CORPDIR=DIRECT
*XDIM_MEMBERSET INACCT=106
*XDIM_MEMBERSET PLANTYPE=BUDGET
*RUNALLOCATION
*FACTOR=USING/TOTAL
*DIM PERIODS WHAT=$INPUT_YEAR$; WHERE=$INPUT_YEAR$.%MNTH%; USING=%PREVY%.%MNTH%,%PREVPREVY%.%MNTH%,%PREVPREVPREVY%.%MNTH%; TOTAL=[YEAR]="%PREVY%" AND [YEAR]="%PREVPREVY%" AND [YEAR]="%PREVPREVPREVY%" AND [YINP]=""
*ENDALLOCATION
*NEXT
P.S. My TIME dimension is named PERIODS
Bluesky| User | Count |
|---|---|
| 15 | |
| 11 | |
| 11 | |
| 4 | |
| 2 | |
| 2 | |
| 2 | |
| 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.