Skip to Content
0

BPC Script Logic for Cashflow

Mar 13 at 02:02 PM

63

avatar image

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 left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Markus Nolden Mar 13 at 02:07 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Mar 14 at 07:33 PM
0

Use PUSH instead of PULL (with LOOKUP)!

Some extra questions:

%TIME_SET% - contain periods of single year?

%ACTUALITY_SET% - single member or multiple members?

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Mar 15 at 02:45 PM
0

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

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Thank you very much. That works

0

Then please ACCEPT the correct answer!

0

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.

0

Under the answer text you can see:

accept.png (3.8 kB)
0

You can use only SINGLE REPLACEPARAM TASK.

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

0
Markus Nolden Mar 15 at 02:28 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded