BPC Script Logic for Cashflow

[former_member456973]

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

[former_member186338]

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

[former_member456973]

Thank you very much. That works

[former_member186338]

Then please ACCEPT the correct answer!

[former_member456973]

How do I accept (where is a button for that)? It was my first question here.

Can you please also answer my next question (additional test) here below? It can be helpful for my next logic scripts. Why does it work in Data Manager with $OT$ and $OA$? OT or OA stand alone will work.

Thank you.

[former_member186338]

Under the answer text you can see:

[former_member186338]

You can use only SINGLE REPLACEPARAM TASK.

But it can assign multiple variables! Please search, I have explaned it hundred of times.

[former_member456973]

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

[former_member186338]

Use PUSH instead of PULL (with LOOKUP)!

Some extra questions:

%TIME_SET% - contain periods of single year?

%ACTUALITY_SET% - single member or multiple members?

[former_member456973]

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