Skip to Content

BPC Script Logic for Cashflow

I have a problem with a Logic script for a Cash flow:

(in advance: we will not use Account Based Calculations (ABCs) for that, please no informations to ACBs)

In general it runs (it calculate the difference of a balance sheet position between the current period and december of prior year):

But it runs only correct if an account has an entry (not blank) in the current year.

Example:

Account A has an entry in current year and in december of prior year, Account B has no(!) entry in current year and an entry in december of prior year

=> The logic script calculate in this case only A, B is zero in this calculation

How can I "say" the logic script that he not ignore blank account in current year (in this case he should not ignore the amount from december of prior year)?

*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET ACTUALITY=%ACTUALITY_SET%
*XDIM_MEMBERSET BANK=NONE
*XDIM_MEMBERSET MATURITIES=NONE

*LOOKUP Consolidation
*DIM OPEN:TIME=TIME.PRIOR //Opening Time (December Prior year)
*DIM OPEN:ACTUALITY=ACTUALITY.CATEGORY_FOR_OPE //Opening Actuality
*ENDLOOKUP

// Inventories
*WHEN ACCOUNT
*IS 1422100,1422200,1424100,1424200,1426100,1426200,1428100,1428200,1429000
*WHEN MOVEMENTTYPE
*IS END
*REC(EXPRESSION=%VALUE%-LOOKUP(OPEN),ACCOUNT=CF240000,ACTUALITY=%ACTUALITY_SET%,MOVEMENTTYPE=END)
*ENDWHEN
*ENDWHEN

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Mar 13 at 02:07 PM

    Additional to example:

    account A: Current period = 60, December prior year = 100, Correct result = -40

    account B: Current period = (Blank), December prior year = 30, Correct result = -30

    Correct result in sum = -70

    But the logic script show as result only -40 (account A), account B he ignores because of the blank in the current period

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 14 at 07:33 PM

    Use PUSH instead of PULL (with LOOKUP)!

    Some extra questions:

    %TIME_SET% - contain periods of single year?

    %ACTUALITY_SET% - single member or multiple members?

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 15 at 02:45 PM

    "TIME_SET is a single period, e.g. 2018.02

    and in TIME-Dimension PRIOR for 2018.02 is 2017.12

    ACTUALITY is a single Category, e.g. TEST

    and in ACTUALITY-Dimension CATEGORY_FOR_OPE for TEST is ACTUAL"

    Then:

    *SELECT(%Y%,YEAR,TIME,ID=%TIME_SET%)
    *SELECT(%J%,ID,TIME,YEAR=%Y% AND MONTHNUM=1)
    *SELECT(%TJ%,TIMEID,TIME,ID=%J%)
    *SELECT(%LTJ%,TIMEID,TIME,TIMEID<%TJ% AND CALC=N)
    *SELECT(%GED%,ID,TIME,TIMEID>=%LTJ% AND CALC=N)
    *SELECT(%D%,ID,TIME,ID=%GED% AND TIMEID<%TJ%)
    
    *SELECT(%A%,CATEGORY_FOR_OPE,ACTUALITY,ID=%ACTUALITY_SET%)
    
    *XDIM_MEMBERSET TIME=%TIME_SET%,%D%
    *XDIM_MEMBERSET ACTUALITY=%ACTUALITY_SET%,%A%
    *XDIM_MEMBERSET BANK=NONE
    *XDIM_MEMBERSET MATURITIES=NONE
    *XDIM_MEMBERSET ACCOUNT=1422100,1422200,1424100,1424200,1426100,1426200,1428100,1428200,1429000
    *XDIM_MEMBERSET MOVEMENTTYPE=END
    
    *WHEN TIME
    *IS %TIME_SET% //2018.02
      *WHEN ACTUALITY
      *IS %ACTUALITY_SET% //TEST
        *REC(EXPRESSION=%VALUE%,ACCOUNT=CF240000)
      *ENDWHEN
    *IS %D% //2017.12
      *WHEN ACTUALITY
      *IS %A% //ACTUAL
        *REC(EXPRESSION=-%VALUE%,ACCOUNT=CF240000,,ACTUALITY=%ACTUALITY_SET%,TIME=%TIME_SET%)
      *ENDWHEN
    *ENDWHEN

    PUSH method!

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 15 at 02:28 PM

    TIME_SET is a single period, e.g. 2018.02

    and in TIME-Dimension PRIOR for 2018.02 is 2017.12

    ACTUALITY is a single Category, e.g. TEST

    and in ACTUALITY-Dimension CATEGORY_FOR_OPE for TEST is ACTUAL.

    What do you mean with PUSH instead of PULL? I have not entered PUSH in the script.

    ...

    ...

    Meanwhile I have test also another way (instead of LOOKUP in the script):

    I changed Data Manager for that as follows (so that e.g. 2017.12 and ACTUAL can additional entered here manually)

    PROMPT(SELECTINPUT,,,,"%TIME_DIM%,%CATEGORY_DIM%")
    PROMPT(TEXT,%OT%,"Enter TIME (yyyy.mm) which should be used for Opening Balance",)
    PROMPT(COMBOBOX,%OA%,"Select ACTUALITY which should be used for Opening Balance",0,,{ACTUAL,BUDGET,MYP1,TEST,FC08})
    INFO(%EQU%,=)

    TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)
    TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)
    'TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,Consolidation)
    TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)
    TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)
    TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,CASHFLOW.LGF)
    TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM,OT%EQU%%OT%)
    TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM,OA%EQU%%OA%)

    I want to use then $OT$ and $OA$ in the Logic script (therefore I have adjusted Logic script). But he only identify $OT$, not $OA$:

    SELECTION = /.../Consolidation/PRIVATEPUBLICATIONS/NOLDEN/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:ACTUALITY|TEST|DIMENSION:TIME|2018.02
    OT = 2017.12
    OA = ACTUAL

    [Message]
    --------------------------------------------------------------

    RUN_LOGIC:Members invalid On Dimension(ACTUALITY)

    ..

    (and in formula log sreen)

    #dim_memberset=4
    BANK:NONE,1 in total.
    MATURITIES:NONE,1 in total.
    TIME:2017.12,1 in total.
    ACTUALITY:$OA$,1 in total.

    ...

    The (adjusted) Logic script for this 2nd test:

    *WHEN ACCOUNT
    *IS 1422100,1422200,1424100,1424200,1426100,1426200,1428100,1428200,1429000
    *WHEN MOVEMENTTYPE
    *IS END
    *REC(EXPRESSION=%VALUE%,ACCOUNT=CF241000,MOVEMENTTYPE = END)
    *ENDWHEN
    *ENDWHEN

    *XDIM_MEMBERSET TIME=$OT$
    *XDIM_MEMBERSET ACTUALITY=$OA$
    *WHEN ACCOUNT
    *IS 1422100,1422200,1424100,1424200,1426100,1426200,1428100,1428200,1429000
    *WHEN MOVEMENTTYPE
    *IS END
    *REC(EXPRESSION=-%VALUE%,ACCOUNT=CF241100,TIME=%TIME_SET%,ACTUALITY=%ACTUALITY_SET%,MOVEMENTTYPE=END)
    *ENDWHEN
    *ENDWHEN

    Add comment
    10|10000 characters needed characters exceeded