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