I am using FUNCTION CC_TOTAL_YEAR to extract the total value of a Fund Center for the year 2015.
Which will be later used for other calculations for now I just want to know if what I am doing is correct or is there another way of doing this in script logic.
Please let me know if I am doing this correctly.
Parameter is CATEGORY=2018_M01
Scope for TIME is all periods between 2015 and 2017.
Attached screenshots from UJKT
LGX:
*XDIM_MEMBERSET FUND_CTR = 3371AA
*XDIM_MEMBERSET DATASOURCE = BW,INPUT
*XDIM_MEMBERSET PAA = BAS(ALL_BPC)
*XDIM_MEMBERSET CMMT_ITEM <> CI_NA
*XDIM_MEMBERSET SCENARIO = SC_0
*XDIM_MEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME=2015.00,2015.01,2015.02,2015.03,2015.04,2015.05,2015.06,2015.07,2015.08,2015.09,2015.10,2015.11,2015.12,2015.13,2015.14,2015.15,2015.16,2016.00,2016.01,2016.02,2016.03,2016.04,2016.05,2016.06,2016.07,2016.08,2016.09,2016.10,2016.11,2016.12,2016.13,2016.14,2016.15,2016.16,2017.00,2017.01,2017.02,2017.03,2017.04,2017.05,2017.06,2017.07,2017.08,2017.09,2017.10,2017.11,2017.12,2017.13,2017.14,2017.15,2017.16
*WHEN
CATEGORY *IS *
*REC(EXPRESSION = (%VALUE%+([CATEGORY].[ACTUAL],[CMMT_ITEM].[CI_ALL],[DATASOURCE].[BW],[FUND].[FUND_ALL],[FUND_CTR].[3371AA],[GL_ACCOUNT].[ACCT_ALL],[PAA].[PAA_ALL],[TIME].[2015.TOTAL],[SCENARIO].[SC_0])))
*ENDWHEN
------------------------------------------------------------------------------------------------------------------------------------- LOG: FILE:\ROOT\WEBFOLDERS\
#dimensions=12 CATEGORY,CC_WBS,CMMT_ITEM,DATASOURCE,FUND,FUND_CTR,GL_ACCOUNT,MEASURES,PAA,SCENARIO,TIME,WBS_ELEMENT #dim_memberset=7 FUND_CTR:3371AA,1 in total. DATASOURCE:BW,INPUT,2 in total. PAA:1.1.1,1.1.2,1.1.3,1.1.4,1.2.1,...86 in total. CMMT_ITEM:111,112,210,310,413,...24 in total. SCENARIO:SC_0,1 in total. CATEGORY:ACTUAL,1 in total. TIME:2015.00,2015.01,2015.02,2015.03,2015.04,...51 in total.
REC :(%VALUE%+([CATEGORY].[ACTUAL],[CMMT_ITEM].[CI_ALL],[DATASOURCE].[BW],[FUND].[FUND_ALL],[FUND_CTR].[3371AA],[GL_ACCOUNT].[ACCT_ALL],[PAA].[PAA_ALL],[TIME].[2015.TOTAL],[SCENARIO].[SC_0]))
CALCULATION BEGIN: QUERY PROCESSING DATA QUERY TIME : 1.00 ms. 228 RECORDS QUERIED OUT. QUERY REFERENCE DATA QUERY TIME : 16.00 ms. 1680 RECORDS QUERIED OUT. CALCULATION TIME IN TOTAL :0.00 ms. 228 RECORDS ARE GENERATED. CALCULATION END. SCRIPT RUNNING TIME IN TOTAL:18.00 s.
*SELECT(%Y%,[YEAR],CATEGORY,[ID]=%CATEGORY_SET%) *SELECT(%REFY%,[REFERENCE_YEAR],CATEGORY,[ID]=%CATEGORY_SET%) *SELECT(%PERIOD%,[ID],TIME,[YEAR]<%Y% AND [YEAR]>=%REFY% AND [CALC]=N) *SELECT(%TIDYDEC%,[ID],TIME,[YEAR]=%REFY% AND [MONTHNUM]=12 AND [CALC]=N) *SELECT(%TIDNEXTYS%,[ID],TIME,[ID]>%TIDYDEC% AND [CALC]=N) *SELECT(%NEXTY%,[YEAR],TIME,[ID]<=%TIDNEXTYS% AND [ID]>%TIDYDEC% AND [CALC]=N) *SELECT(%NEXTYID%,[YEAR],TIME,[YEAR]=%NEXTY% AND [CALC]=N) *SELECT(%NEXTYDEC%,[TIMEID],TIME,[YEAR]=%NEXTY% AND [MONTHNUM]=12 AND [CALC]=N) *SELECT(%YEAR3%,[YEAR],TIME,[YEAR]<%Y% AND [ID]>%NEXTYDEC% AND [CALC]=N) *XDIM_MEMBERSET FUND_CTR = 3371AA *XDIM_MEMBERSET DATASOURCE = BW,INPUT *XDIM_MEMBERSET PAA = BAS(ALL_BPC) *XDIM_MEMBERSET CMMT_ITEM <> CI_NA *XDIM_MEMBERSET SCENARIO = SC_0 *XDIM_MEMBERSET CATEGORY = ACTUAL //*XDIM_MEMBERSET TIME=%YEAR3% *XDIM_MEMBERSET TIME=%PERIOD% *FUNCTION CC_TOTAL_YEAR = ([CATEGORY].[ACTUAL],[CMMT_ITEM].[CI_ALL],[DATASOURCE].[BW],[FUND].[FUND_ALL],[FUND_CTR].[3371AA],[GL_ACCOUNT].[ACCT_ALL],[PAA].[PAA_ALL],[TIME].[2015.TOTAL],[SCENARIO].[SC_0]) *WHEN CATEGORY *IS * *REC(EXPRESSION = (%VALUE%+CC_TOTAL_YEAR)) *ENDWHEN
Sorry, but you are asking questions and not responding...
https://answers.sap.com/questions/339161/forecast-year-into-monthly-by-getting-the-3-year-m.html
Also, your questions are not clear!
Please read:
https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/
P.S. Function can be used to replace text in your script. But the benefit is minimal and sometime it's even reduce readability.
It's not a real function like in other languages, just a text replacement
Hi Vadim
Previous ask was too messy. I am just doing step by step approach. One of my colleagues suggested to use the FUNCTION to retrieve the value of a specific fund center for a specific member. I have developed that code , but I am not sure how I can see this value to check whether the code is working or not.
Do you understand my answer:
"Function can be used to replace text in your script. But the benefit is minimal and sometime it's even reduce readability.
It's not a real function like in other languages, just a text replacement"
"FUNCTION to retrieve the value..." incorrect wording! Nothing is retrieved.
Just function name in the script is replaced by function text, nothing more!
So what can I use to get the $ value of a specific fund center (3371AA) for the time member (2015_total). All values the remaining the same in the FUNCTION
Looks like you don't understand....
You can't "get the value" - the idea is incorrect!
In REC statement you can use tuple expression like:
([CATEGORY].[ACTUAL],[CMMT_ITEM].[CI_ALL],[DATASOURCE].[BW],[FUND].[FUND_ALL],[FUND_CTR].[3371AA],[GL_ACCOUNT].[ACCT_ALL],[PAA].[PAA_ALL],[TIME].[2015.TOTAL],[SCENARIO].[SC_0])
It's a combination of dimensions and members. If some dimension is not specified in the tuple expression then the member for this dimension will come from the current record of WHEN/ENDWHEN loop.
Using function you reduce the code readability and slightly increase script processing time (to perform replacement)!
P.S. If you need to reference the member using property of another member - you have to use LOOKUP.
How do I store a value and then do a calculation on it.
for a given fund_center 3371AA give me a total on ACTUALS for 2015. I need to store this somewhere temporarily.
then give me the fund_center value for 3371AA where TIME.MonthNum = '01' and CATEGORY.properties("YEAR")="2015"
then get the rate by dividing JAN VALUE/TOTAL VALUE
I am new to bpc logic. I have limited knowledge.
"How do I store a value and then do a calculation on it." - there is no way to store value in BPC script logic!
You can READ value in REC using tuple expression or LOOKUP.
Repeating the same!
P.S. Do you know ABAP? If yes, then debug script execution in ABAP debugger - you will understand how it's working.
Hi Vadim
If I cannot store data in BPC script logic. How do i then tackle this problem? I need to calculate the weighted avg rate for every month and then allocate it to all cost center for the current forecast year.
business logic has it that jan rate = (jan actuals for current year -1 + jan actuals current year -2 + jan actuals current year -3) / (total actuals for current year -1 + total actuals for current year -2 + total actuals for current year -3)
I will show you an example!
Ok thanks
Use some special DATASOURCE - for example AVECALC to store calculated rates.
//%YCUR%=2017, %YMINUS1%=2016, %YMINUS2%=2015, %YMINUS3%=2014 *FOR %M%=01,02,03,04,05,06,07,08,09,10,11,12 *XDIM_MEMBERSET TIME=%YMINUS1%.%M%,%YMINUS2%.%M%,%YMINUS3%.%M% *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.%M%,DATASOURCE=AVECALC) *ENDWHEN *NEXT
As a result in current year you will have accumulated data per month for 3 previous years.
Then to allocate use:
*RUNALLOCATION *FACTOR=USING/TOTAL *DIM DATASOURCE WHAT=BW; WHERE=<<<; USING=AVECALC; TOTAL=<<< *DIM TIME WHAT=%YCUR%.INP; WHERE=BAS(%YCUR%.TOTAL); USING=<<<; TOTAL=<<< *ENDALLOCATION
Assuming %YCUR%.INP contain total value to allocate to months.
Hope you understand the idea!
P.S. Start your tests with a simple copy of ENVIRONMENTSHELL model PLANNING.
Hi Vadim
Thanks for the tip. Will this run for all cc or the ones scoped?
When running it in UJKT in simulation I am getting this error
APPLICATION:RCN_HIGH_LEVEL [INFO] GET_DIM_LIST(): I_APPL_ID="RCN_HIGH_LEVEL", #dimensions=12 CATEGORY,CC_WBS,CMMT_ITEM,DATASOURCE,FUND,FUND_CTR,GL_ACCOUNT,MEASURES,PAA,SCENARIO,TIME,WBS_ELEMENT #dim_memberset=7 FUND_CTR:3371AA,1 in total. DATASOURCE:BW,INPUT,2 in total. PAA:1.1.1,1.1.2,1.1.3,1.1.4,1.2.1,...86 in total. CMMT_ITEM:111,112,210,310,413,...24 in total. SCENARIO:SC_0,1 in total. CATEGORY:ACTUAL,1 in total. TIME:%YMINUS1%.01,%YMINUS2%.01,%YMINUS3%.01,3 in total. REC :%VALUE% CALCULATION BEGIN: QUERY PROCESSING DATA UJO_READ:Members invalid On Dimension(TIME)
Attached are the 2018 Time Dimension Members
When I validate the following code is coming up LGX: *XDIM_MEMBERSET FUND_CTR = 3371AA *XDIM_MEMBERSET DATASOURCE = BW,INPUT *XDIM_MEMBERSET PAA = BAS(ALL_BPC) *XDIM_MEMBERSET CMMT_ITEM <> CI_NA *XDIM_MEMBERSET SCENARIO = SC_0 *XDIM_MEMBERSET CATEGORY = ACTUAL *XDIM_MEMBERSET TIME=%PERIOD% *XDIM_MEMBERSET TIME=%YMINUS1%.01,%YMINUS2%.01,%YMINUS3%.01 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.01,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.02,%YMINUS2%.02,%YMINUS3%.02 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.02,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.03,%YMINUS2%.03,%YMINUS3%.03 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.03,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.04,%YMINUS2%.04,%YMINUS3%.04 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.04,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.05,%YMINUS2%.05,%YMINUS3%.05 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.05,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.06,%YMINUS2%.06,%YMINUS3%.06 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.06,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.07,%YMINUS2%.07,%YMINUS3%.07 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.07,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.08,%YMINUS2%.08,%YMINUS3%.08 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.08,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.09,%YMINUS2%.09,%YMINUS3%.09 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.09,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.10,%YMINUS2%.10,%YMINUS3%.10 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.10,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.11,%YMINUS2%.11,%YMINUS3%.11 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.11,DATASOURCE=AVECALC) *ENDWHEN *XDIM_MEMBERSET TIME=%YMINUS1%.12,%YMINUS2%.12,%YMINUS3%.12 *WHEN TIME *IS * *REC(EXPRESSION=%VALUE%,TIME=%YCUR%.12,DATASOURCE=AVECALC) *ENDWHEN *WHEN CC_WBS *IS * *RUNALLOCATION *FACTOR=USING/TOTAL *DIM DATASOURCE WHAT=BW; WHERE=<<<; USING=AVECALC; TOTAL=<<< *DIM TIME WHAT=%YCUR%.INP; WHERE=BAS(%YCUR%.TOTAL); USING=<<<; TOTAL=<<< *ENDALLOCATION *ENDWHEN
This is not a full code, you have to add missing parts yourself! I will not do your job instead of you...
And I have told you: Start your tests with a simple copy of ENVIRONMENTSHELL model PLANNING.
You have to understand the logic....