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

10|10000 characters needed characters exceeded

• Mar 13, 2018 at 02:07 PM

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

10|10000 characters needed characters exceeded
• Mar 14, 2018 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?

10|10000 characters needed characters exceeded
• Mar 15, 2018 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!

10|10000 characters needed characters exceeded

You can use only SINGLE REPLACEPARAM TASK.

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

• Mar 15, 2018 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%,=)

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