Ternary Operator - Wrong Calculation Results

[Eyal_Feiler]

Hi Experts,

We have logic that rounds – depending on greater than or less than 0 and then inner logic <.5 and >.5, Script is below.

The logic works most of the time except in some circumstances – tried isolating to multiple dimension members within the scoped dimensions but it’s not consistent.

Working example

3.25 results in (.25) so that 3.25-.25 = 3

3.60 results in .4 = 4.0

(2.40) results in +.40 = (2.0)

Error Example

3.3 results in .7 = (3.3+.7)

Again can’t determine why this occurs - some dimensions are the breakout of the rounding e.g. ENTITY,ACCOUNT,INTERCO, CURRENCY,FLOW, while most are summarized – total of COUNTRY = CO_ALL.

Caps in the script are correct as per the dimension parents.

Any ideas?

Here is the script.

Also tried splitting the REC statements to 2 statements with separate scoping and commit. Same result. The logic is getting tied up in the inner loop of the first REC statement as an example

//2B_ROUND_RND23N.LGF

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET CURRENCY = %CURRENCY_SET%

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET AUDITID = BAS(AD3005) // Other dimensions *XDIM_MEMBERSET ACTIVE_INGREDIEN=AI_All

*XDIM_MEMBERSET ACTIVITY=AC_All

*XDIM_MEMBERSET BANK=BA_All

*XDIM_MEMBERSET COST_CENTER=CC_ALL

*XDIM_MEMBERSET COUNTRY=CO_All

*XDIM_MEMBERSET INTERCO=BAS(I_ALL)

*XDIM_MEMBERSET PRODUCTION_LINE=PL_ALL

*XDIM_MEMBERSET SUBGLOBALRGN=GR_All

*XDIM_MEMBERSET TC=TC_ALL

*XDIM_MEMBERSET FLOW =BAS(END),F99

*WHEN SCOPE *IS S_NONE

// SPLIT POSITIVE AND NEGATIVE

// greater than 0

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? -%VALUE%%1:1-%VALUE%%1):0, AUDITID=ADRND2,ACTIVE_INGREDIEN=AI_NONE,ACTIVITY=AC_NONE,BANK=BA_NONE,COST_CENTER=CC_NONE,COUNTRY=CO_NONE,PRODUCTION_LINE=PL_NONE,SUBGLOBALRGN=GR_NONE,TC=TC_NONE)

// Less than 0

*REC(EXPRESSION=(%VALUE% <0)?((-%VALUE%%1 < 0.5 )? -%VALUE%%1:-1-%VALUE%%1):0, AUDITID=ADRND3,ACTIVE_INGREDIEN=AI_NONE,ACTIVITY=AC_NONE,BANK=BA_NONE,COST_CENTER=CC_NONE,COUNTRY=CO_NONE,PRODUCTION_LINE=PL_NONE,SUBGLOBALRGN= GR_NONE,TC=TC_NONE)

*ENDWHEN

*COMMIT

This was a long posting with guidance on ternary – where I tried to create in one shot but ended up splitting the ternary logic.

https://answers.sap.com/questions/507235/rounding-and-offsetting-to-different-datasource-au.html?chi...

Thanks in advance.

Regards,

Eyal

[Eyal_Feiler]

Hi Vadim,

Found the problem with a little help....

Issue was the scoping of the AUDITID - I was using BAS(PARENT) instead of

*XDIMMEMBERSET AUDITID = PARENT

Now it's working as we want!

Thanks for your help.

Regards

Eyal

[former_member186338]

I still think that your approach for rounding is wrong.

[former_member186338]

In general - where you can see rounding errors? Rounding errors are results of multiplication/division:

- Currency translation

- Consolidation

- Allocation

- Script logic with multiplication/division

Can be solved using write back badi performing rounding

[Eyal_Feiler]

Hi Vadim,

Yes we tried that but what happens is this – on a detail level.

ACCT_1234 BANK_1:10.3 rounded to 10

ACCT_1234 BANK_2: 10.14 rounded to 10

ACCT_1234 BANK_3: 10.16 rounded to 10

Total30.6 rounded to 30

If we summarize without a bank then the total is 30.6 rounded to 31.This is what we are trying to accomplish

Regards,

Eyal

[former_member186338]

In finance rounding is performed on the transaction level and is based on currency (number of decimal digits).

If you have some base figures then sum has to be correct. Look on my VAT sample!

[former_member186338]

"Thinking is that I don’t want the rounding to occur on every single row of BANK dimensions and others – too detailed" - Sorry, but this is the wrong idea to my mind. You have to perform rounding on the individual records, not totals. It's incorrect from finance point of wiew...

Sample:

You have invoice with 5 product lines and VAT 18%

Total VAT for invoice is not equal to TotalInvoice*0.18, but has to be calculated for each line due to rounding.

[former_member186338]

Sample in Excel:

[Eyal_Feiler]

Hi Vadim,

We are using BPC NW 10.1 SP11 - Using K2CalcEngline=JS (Javascript)

TC_All is a parent member as are the dimensions below (except Interco of course).

Thinking is that I don’t want the rounding to occur on every single row of BANK dimensions and others – too detailed.Intent is to summarize on these dimensions.

In my sample test I used multiple banks, multiple Sublglobalrgn etc. and it worked.

In the data set of 2000 there are multiple values of these dimension members TC, Bank etc.

// Other dimensions *XDIM_MEMBERSET ACTIVE_INGREDIEN=AI_All

*XDIM_MEMBERSET ACTIVITY=AC_All

*XDIM_MEMBERSET BANK=BA_All

*XDIM_MEMBERSET COST_CENTER=CC_ALL

*XDIM_MEMBERSET COUNTRY=CO_All

*XDIM_MEMBERSET INTERCO=BAS(I_ALL)

*XDIM_MEMBERSET PRODUCTION_LINE=PL_ALL

*XDIM_MEMBERSET SUBGLOBALRGN=GR_All

*XDIM_MEMBERSET TC=TC_ALL

Regards,

Eyal

[Eyal_Feiler]

Hi Vadim,

Thanks for the response.

1. When I built the test I used 10 rows as you did and it successfully ran (including for AST, LEQ, INC and EXP account types as well as multiple rows for the same accounts with different or same dimensions (e.g. country CO_01 CO_02 etc).

So the script runs successfully.

Ran with selecting TC_ALL, CO_ALL etc as well as specific dimension scoping.

1. The error occurs when I ran for an entity with 2,000 data rows and suddenly almost all worked with maybe 10 rows generating the opposite result.3.4 = 3.0. There was no correlation in the error as to account type, greater than less than etc or data structure.

   An example of an error: Rows had multiple dimension values within the all e.g. 25 rows for same Account with 2 country dimension members (CO_USA, CO_UK) and several TC dimension members (TC_EUR, TC_USD, TC_NONE). However, other rows in the set with similar data did work.

2. I took the script logic and switched from TC_All to TC_EUR for the same set to see if the error occurred and the erorr did not occur.So I believe there is something with my scoping or more likely a bug – why would the logic select more than (e.g. 3.4 round to 4) when clearly the sum of the amounts are 3.4)?
3. What information where you missing?

4. The logic works (thanks to your help in a prior post!).

   Any ideas?

   Regards,

   Eyal

[former_member186338]

Please read: https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

Even version info is not provided!

[former_member186338]

P.S. What do you mean by:

*XDIM_MEMBERSET TC=TC_ALL

TC_ALL - is it a base member?

[former_member186338]

Just tested:

*XDIM_MEMBERSET TIME=2007.01,2007.02,2007.03,2007.04
*XDIM_MEMBERSET AUDITTRAIL=Input
*XDIM_MEMBERSET CATEGORY=Budget
*XDIM_MEMBERSET ENTITY=DE
*XDIM_MEMBERSET PRODUCT=ProductA
*XDIM_MEMBERSET INTERCO=ThirdParty
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET  ACCOUNT=PL110 //ACCTYPE=EXP
*WHEN ACCOUNT 
*IS *
*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? -%VALUE%%1:1-%VALUE%%1):0, ACCOUNT=PL120)
*REC(EXPRESSION=(%VALUE% <0)?((-%VALUE%%1 < 0.5 )? -%VALUE%%1:-1-%VALUE%%1):0, ACCOUNT=PL120)
*ENDWHEN

Initial data:

Script result:

...
DATA TO WRITE BACK:
ACCOUNT    AUDITTRAIL    CATEGORY    ENTITY    INTERCO    PRODUCT    RPTCURRENCY    TIME    SIGNEDDATA
PL120    Input    Budget    DE    ThirdParty    ProductA    LC    2007.01    0.25-
PL120    Input    Budget    DE    ThirdParty    ProductA    LC    2007.02    0.40 
PL120    Input    Budget    DE    ThirdParty    ProductA    LC    2007.03    0.40 
PL120    Input    Budget    DE    ThirdParty    ProductA    LC    2007.04    0.30-
4  RECORDS HAVE BEEN WRITTEN BACK.
...

Report result:

Use simple models to test logic!

[former_member186338]

Please read: https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

And provide missing info!

3.3 results in .7 = (3.3+.7)

May be 3.3 is for INC/LEQ account? Then it will be (3.3)...