0

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

Oct 31, 2017 at 06:47 PM

44

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

```
capture1.jpg (39.8 kB)

Vadim Kalinin Oct 31, 2017 at 06:51 PM
0

Sorry, but you are asking questions and not responding...

Also, your questions are not clear!

Share
Vadim Kalinin Oct 31, 2017 at 07:02 PM
0

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

Share
Fahad Qureshi Oct 31, 2017 at 07:08 PM
0

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.

Show 10 Share

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.

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

Vadim Kalinin Nov 01, 2017 at 03:12 PM
0

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.

Show 3 Share

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

capture8.jpg (146.1 kB)
```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

```