Script Logic - Divide by zero

[Former Member]

Hello,

I'm currently working with SAP BPC NW 10.1 SP5.

I'm trying to create a script logic to determine the value of an account based on a sum and a division of another accounts. When I test this, if the denominator of the division is 0 or non-existing, the whole result is 0, despite the value of the account I'm using in the sum.

Only for testing purposes, I tried to use values instead of accounts, for example:

*REC(ACCOUNT=PESO_PRODUCAO_DIST_M, EXPRESSION=1 + (2/0)  )

If I use this, I expected the result to be 1, but it is zero.

*REC(ACCOUNT=PESO_PRODUCAO_DIST_M, EXPRESSION=1 + 2 )

If I use this, the result is 3 as expected.

Is this any special behavior regarding division by zero? Are there any workaround, without having to use the conditional logic?

Thanks!

[former_member186338]

Hi Lara,

Yes, with script logic the division by zero in some record (or with missing record) produce zero!

But why you want to have "despite the value of the account I'm using in the sum"? Division by zero produce infinite value

You have to use conditional operator - ternary operator in REC:

*REC(EXPRESSION=(%VALUE%==0) ? VALUEIFZERO : 1/%VALUE%,ACCOUNT=PESO_PRODUCAO_DIST_M)

Please explain the logic you want to achieve!

Vadim

[Former Member]

Hi Vadim,

For example, consider this:

Value of Account1 = 10

Value of Account2 = 15

Value of Account3 = 0

*REC(ACCOUNT=Account4,EXPRESSION=[ACCOUNT].[Account1] + [ACCOUNT].[Account2]/[ACCOUNT].[Account3])

This is the logic I what to achieve and the result I expected was 10, but what I obtained was zero.

After this, I tried this instruction, in order to verify if it was a different problem, and I obtained the expected result, 25.

*REC(ACCOUNT=Account4,EXPRESSION=[ACCOUNT].[Account1] + [ACCOUNT].[Account2])

Lara

[former_member186338]

Please show the FULL script and describe the business logic you want to achieve.

In general, the idea of line like:

*REC(ACCOUNT=Account4,EXPRESSION=[ACCOUNT].[Account1] + [ACCOUNT].[Account2]/[ACCOUNT].[Account3])

is bad! Problems with loop scope!

Please read:

Vadim

[Former Member]

*XDIM_MEMBERSET ENTITY = AA

*XDIM_MEMBERSET CATEGORY = BUDGET

*XDIM_MEMBERSET TIME = BAS(2015.TOTAL)

*XDIM_MEMBERSET ACCOUNT = Account1,Account2,Account3

*WHEN ACCOUNT

*IS Account1

  *REC(ACCOUNT=Account4,EXPRESSION=[ACCOUNT].[Account1] + [ACCOUNT].[Account2]/[ACCOUNT].[Account3])

*ENDWHEN

The Account1 represents a factor that was previously calculated and Account2/Account3 represents another factor that I want to sum to this, and obtain a final factor (Account4).

[former_member186338]

Due to this:

*XDIM_MEMBERSET ACCOUNT = Account1,Account2,Account3

The REC will be calculated 3 times with tripled result! Incorrect!

What is the business meaning of the factors? Do you understand the question?

[Former Member]

I only have Account1 on the *WHEN, and I am using *WHEN_REF_DATA = TRANS_DATA, so it will only be calculated one time.

Account 1 represents the weight of production of the Entity specified on the total of entities. Account 2 represents the production of the Entity specified in the previous year and Account3 the production of all entities in the previous year.

We have this stored in specific accounts due to other requirements. The value obtained by this instruction will be divided by two after to obtain the average of the factors.

The problem is when I don't have Account3 filled, the Account4 value should be the factor in Account1, and it is not what I'm obtaining.

[former_member186338]

"Account 1 represents the weight of production of the Entity specified on the total of entities. Account 2 represents the production of the Entity specified in the previous year and Account3 the production of all entities in the previous year." - not clear and not related to the script presented...

I think that the complete logic has to be redeveloped!

Just for test you can use the following REC:

*RECEXPRESSION=([ACCOUNT].[Account3]==0) ? [ACCOUNT].[Account1] : [ACCOUNT].[Account1] + [ACCOUNT].[Account2]/[ACCOUNT].[Account3],ACCOUNT=Account4)

but the described logic is done by dimension member formulas etc...

Vadim