Skip to Content

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

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Oct 31, 2017 at 06:51 PM
    Add comment
    10|10000 characters needed characters exceeded

  • Oct 31, 2017 at 07:02 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 31, 2017 at 07:08 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 01, 2017 at 03:12 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

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