Conditional Logic/Boolean/*IIF

[Former Member]

Hi Experts,

I'm trying to pick up a value from an Account (TotCapexNominal) and post it to another account in another application. The hard part is this: I want to post it to the time period that matches the value stored in another Account (CapnMth) provided by the user.

My approach is to use conditional logic, but I'm finding that although the formulas are happy with Greater Than and Less Than, they don't seem to like Equals. I've tried various approaches with standard conditional logic (as per below), boolean formulas and *IIF. No luck. My code below is returning the following error:

UJK_VALIDATION_EXCEPTION:Unknown Dimension Name in Keyword: "2016009):

If I put either a Greater Than or a Less Than in the bold section I don't get an error ... but I don't get the result I need either

I'm using BPC7.5 NW with SP15

*SELECT($SRCVER$,ID,Version,ID=FCT2016Q3M2)

*SELECT($TGTVER$,ID,Version,ID=BUD2016V1)

*SELECT(%FCTPERS%,ID,Time,CALC=N AND ID>2016008 AND YEAR0000.Generic AND ID<>SELECT_TIME)

*SELECT(%CCS%,ID,CostCentre,TYPE=CC AND CALC=N AND ID<>CCGeneric AND ID<>SELECT_CC AND ID<>SELECT_TCC)

*SELECT(%ASSETCAT%,ID,AssetClass,CALC=N AND ID<>LAND AND ID<>ACGeneric AND DEPN_EXP_ACC<>'')

*XDIM_MEMBERSET Version=$SRCVER$

*XDIM_MEMBERSET Time=%FCTPERS%

*XDIM_MEMBERSET CostCentre=%CCS%

*XDIM_MEMBERSET DataSrc=BAS(Result)

*XDIM_MEMBERSET AssetClass=%ASSETCAT%

*XDIM_MEMBERSET AccountC=TotCapexNominal

*DESTINATION_APP=BSCF

*ADD_DIM Flow = F_CLO

*RENAME_DIM AccountC=AccountSKF

*SKIP_DIM = ProjectID

*SKIP_DIM = LineItem

*SKIP_DIM = AssetClass

*FOR %FCTPER%=%FCTPERS%

*WHEN ASSETCLASS

*IS *

*REC(FACTOR=(([ACCOUNTC].[CapnMth],[DATASRC].[Input],[TIME].[0000.Generic])=%FCTPER%),VERSION=$TGTVER$,COSTCENTRE=COSTCENTRE.FCGROUP,DATASRC=Input,ACCOUNTC=315000,TIME=%FCTPER%)

*ENDWHEN

*NEXT

[former_member186338 · Accepted solution]

Sorry, but you are using some strange syntax...

First - what do you mean by (in bold):

*SELECT(%FCTPERS%,ID,Time,CALC=N AND ID>2016008 AND YEAR0000.Generic AND ID<>SELECT_TIME)

Please provide the list of TIME dimension members (dimension administration).

And explain the calculation logic in details... "I want to post it to the time period that matches the value stored in another Account (CapnMth) provided by the user." - not clear!

[Former Member · Accepted solution]

Sorry, should be YEAR<>0000.Generic.

I'll do my best to explain the reasoning. Let me know if this is still not clear. Users provide details of capital expenditure amounts for various projects by month. They also provide the Capitalisation Month (CapnMth).

The values provide go to the WIP account until the capitalisation date. At this date the amounts come out of the WIP account to another PPE account in the ledger.

My objective here is to remove the accumulated spend from WIUP and post it to the WIP account in the month the user has nominated as the Capitalisation Month.

I hope this makes better sense.

Paul

[former_member186338 · Accepted solution]

Sorry, but still not clear. You have to provide sample with figures and all members used. Show some table...

[former_member186338 · Accepted solution]

Some other strange things:

([ACCOUNTC].[CapnMth],[DATASRC].[Input],[TIME].[0000.Generic]) is the value

%FCTPER% is the member id

How do you want to compare ...

Anyway, for Capex calculations script logic is not the right tool. Create a custom logic badi.

Vadim

[Former Member · Accepted solution]

The Capex Model has 8 dimensions:

• Version
• Time
• CostCentre
• DataSrc
• AccountC
• AssetClass
• ProjectID
• LineItem

In a forecast version, users will provide Capex values for each month in the forecast Period, and for each relevant combination of ProjectID/LineItem/CostCentre/AssetClass. The monthly values will be stored against ACCOUNTC.StdCapexNominal.

The Total for each ProjectId/LineItem/CostCentre/AssetClass combination is stored against ACCOUNTC.TotalCapexNominal.

TIME is the month (e.g. 2016010) for the user keyed values, but is 0000.Generic for the TotCapexNominal amounts.

The DATASRC member is always Input.

Suppose there is one Capital Project, which has 22,000 stored against the following dimensions:

DIMENSION	MEMBER
VERSION	FCT2016Q3
PROJECTID	10001
LINEITEM	LI01
COSTCENTRE	10600
ASSETCLASS	PLNT
DATASRC	Input
ACCOUNTC	TotCapexNominal
TIME	0000.Generic

It also has the value 2017002 stored against

DIMENSION	MEMBER
ACCOUNTC	CapnMth

otherwise the same dimensions as above.

I want to write the 22,000 to TIME=2017002 in another cube. The Other dimensions are:

DIMENSION	MEMBER
VERSION	user defined
COSTCENTRE	COSTCENTRE.FCGROUP
DATASRC	Input
ACCOUNTSKF	ASSETCLASS.PPE_ACC
INTERNALORDER	IOGeneric

The part I am struggling with is matching the value stored on ACCOUNTC=CapnMth with TIME in the destination. It seems a straightforward problem to solve with a boolean or ternary operator, but none of them seem to accept =. At least not in UJKT.

[Former Member · Accepted solution]

Vadim

Yes, the value stored as per the MDX string is keyed by the user. It indicates the month the Project is capitalised. The keyed value must correspond to a member in the TIME dimension.

So I need to match up the user keyed values with the time dimension members a I loop through %FCTPERS%.

Unfortunately I am not authorised to create BADIs (and in any case have no ABAP knowledge). I will need to engage a consultant if this can't be done in script logic.

Paul

[former_member186338 · Accepted solution]

Strange logic from the financial point of view...

In the traditional capex planning projects user enter the capital amount of the item in some specific asset class in the month when depreciation begin. Some special account contains number of depreciation months for each asset class. Then some special routine (custom logic badi) will calculate period depreciation values and store the results in respective months...

Sample:

Asset with amount 22000 was stored with ASSETCLASS PLNT (36 months depreciation) in 2017002

Month depreciation 22000/36=611

611 has to placed from 2017002 to 2020001 (36 periods)

Vadim

P.S. And the badi is required!

[Former Member · Accepted solution]

The depreciation expense was the (relatively) easy bit. I've done that all in script logic (with no small amount of help from yourself).But there's also the WIP movements: moving the accumulated Capex per project from the WIP account to the PPE account in the balance sheet upon completion.

There's also the update of accumulatd depreciation accounts in the balance sheet. This is the flipside of the depreciation expense, except these numbers accumulate.

Then there's the updating the update of PPE historical costs and accumulated depreciation accounts

for asset disposals.

I reckon I could do it all in script logic, except the boolean and ternary formulas will read greater than and less than, but not equals.

Tomorrow I'm going to try multiplying a boolean if the month is <TMVL,TIME,1 by another boolean if month is >TMVL,TIME,-1.

I'm getting desparate. I really don't want to resort to BADI.

But the formulas not accepting equals? What's all that about?

[former_member186338 · Accepted solution]

"boolean or ternary operator, but none of them seem to accept =. At least not in UJKT." - just for information, the supported conditional syntax in REC is:

(A>B)? C : D (C if true, D if false)

In BPC NW 10 it works correctly with ">",">=","<","<=","==" Not working with "<>"

In BPC NW 7.5 SP17 it works correctly only with "<" and ">"

[former_member186338 · Accepted solution]

You can't apply TMVL to the current record...

"The depreciation expense was the (relatively) easy bit." - not sure it's correct...

[former_member186338 · Accepted solution]

At the end of the day you will go to the badi solution

[Former Member · Accepted solution]

I'll apply TMVL to %FCTPER% and compare it to the value returned by the MDX string.

Desparate, I know.

[former_member186338 · Accepted solution]

Sorry, but to my mind the whole idea with entering date as a number is bad...

[Former Member · Accepted solution]

But only the user can tell me the capitalisation date, and the user can only enter a number. What other options are there?

[former_member186338 · Accepted solution]

user can enter amount at capitalization date!

[Former Member · Accepted solution]

That would require two columns for each period. One for the expenditure and another for the capitalisation amount.

And the capitalisation columns, although appearing against every period, would only be populated once for each project

Moreover, the users would need to add WIP and accumulated capex to get the total capitalisation amount. The users are the CFO, CEO and other people with titles starting with 'C'. They don't even like providing information once. Twice? I've got no chance.

My very first approach was to do these calculation on a second hidden sheet. This seems perfectly sound, but in my environment, using array formulas in BPC often leads to a crash. Particularly when the arrays reference a second sheet. I tried moving the arrays back to the same sheet, but still got occasional crashes. I've never looked any further to see if other users have this problem with arrays in BPC.

[Former Member · Accepted solution]

Got It!

*FOR %FCTPER%=%FCTPERS%

  *WHEN ASSETCLASS

  *IS *

    *REC(EXPRESSION=((%VALUE%TMVL(-1,%FCTPER%))*([AccountC].[TotCapexNominal])),VERSION=$TGTVER$,COSTCENTRE=COSTCENTRE.FCGROUP,DATASRC=Input,ACCOUNTC=315000,TIME=%FCTPER%)

  *ENDWHEN

*NEXT

[former_member186338 · Accepted solution]

Sorry, but Capex planning is traditionally done on the separate sheet. Looks like you are over-complicating things.

Vadim

P.S. Never use array formulas Very dangerous

[former_member186338 · Accepted solution]

May be you mean:

*REC(EXPRESSION=((%VALUE%>TMVL(-1,%FCTPER%))*(%VALUE%<TMVL(1,%FCTPER%))*([AccountC].[TotCapexNominal])),VERSION=$TGTVER$,COSTCENTRE=COSTCENTRE.FCGROUP,DATASRC=Input,ACCOUNTC=315000,TIME=%FCTPER%)

[Former Member · Accepted solution]

Yes that's actually what I pasted, but everything between the < and the > is missing because it was interpreted as a HTML tag. That sometimes happens when I copy and paste in my office environment. Never at home.

When you say never use array formulas, do you mean in BPC, or in general?

[former_member186338 · Accepted solution]

In general Array formulas with CtrlShftEnter can be easily corrupted by users!

Use FireFox to post to this forum...