Using FUNCTION formula to retrieve Fund Center total value for the year

[former_member211767]

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

[former_member186338]

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.

[former_member211767]

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

[former_member211767]

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

[former_member186338]

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....

[former_member211767]

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.

[former_member186338]

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!

[former_member211767]

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

[former_member186338]

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)!

[former_member186338]

P.S. If you need to reference the member using property of another member - you have to use LOOKUP.

[former_member211767]

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.

[former_member186338]

"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!

[former_member186338]

P.S. Do you know ABAP? If yes, then debug script execution in ABAP debugger - you will understand how it's working.

[former_member211767]

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)

[former_member186338]

I will show you an example!

[former_member211767]

Ok thanks

[former_member186338]

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

[former_member186338]

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/