Script Logic using when Condition

[Former Member]

Hi,

Im working on BPC 7.5 SP04 MS and I cant figure out how to make this logic work:

1. Cuenta members (account) with the ivaacred property, are multiplied by a certain value and stored in the account 13010A

*WHEN TIEMPO

*IS 2009.Jan, etc etc.....

*WHEN CUENTA.IVAACRED

*IS "Y"

*REC(EXPRESSION=%VALUE% * GET(METRICAS="IND.IVA"),CUENTA="13010A")

2. When the data values for the cuenta 13010A are <= 0, then multiply them by zero and post them in the same account, else leave them the same way.

*WHEN CUENTA

*IS 13010A

IS <= 0

*REC (EXPRESSION = %VALUE% * 0, CUENTA="13010A")

*ENDWHEN

Thanx in advance

Velázquez

[Former Member]

Hi Velázquez,

For the 1st one, you can try;

*WHEN CUENTA.IVAACRED

*IS "Y"

*REC(FACTOR=GET(METRICAS="IND.IVA"),CUENTA="13010A")

*ENDWHEN

*COMMIT

For the 2nd one, you can try;

*XDIM_MEMBER CUENTA=13010A

*WHEN SIGNEDDATA

*IS <0

*REC(FACTOR=0)

*ENDWHEN

*COMMIT

Hope this helps.

Karthik AJ

[Former Member]

Hi Karthik,

Thanx for the response, however this error is shown:

Validation status of executable file: Failed

- Invalid line in WHEN structure: *XDIM_MEMBER CUENTA=13010A

Validation status of syntax: Success

Hope you can help me

[Former Member]

Hi Velazquez,

Just now noticed you are using BPC 75.

In BPC 75, we are facing issues with the SIGNEDDATA in *WHEN statement.

Could you please try this?

*XDIM_MEMBER CUENTA=13010A

*WHEN GET(CUENTA=13010A)

*IS <0

*REC(FACTOR=0)

*ENDWHEN

*COMMIT

Please let me know if it works.

Karthik AJ

[Former Member]

Karthik,

Thanx so much for the answer !! I'll run this and will be back with feedback because i tried something quite like this logic before but was not succesful.

Regards

Velázquez

[Former Member]

Hi Velazquez,

One small change in the code. Forgot to use the double quotes to define the members in the GET function. So the code for the WHEN statement, will be;

*WHEN GET(CUENTA="13010A")

Hope this helps.

Karthik AJ

[Former Member]

Karthik,

I appreciate a lot your help, for starters I forgot to say that I am not using a prompt, therefore every single member (time, account, etc) is typed up in the script which translates into TIME CONSUMING.

Also, the logic for my scripts is really basic and the text is really extended.

In this case, at the beggining of the script taxes are calculated and the part that you suggested is supposed to go at the end (since previous calcs are needed).

The issue is that typing the code at the end of the script provokes this:

Validation status of executable file: Failed

- Invalid line in WHEN structure: *XDIM_MEMBER CUENTA=13010A

Validation status of syntax: Success

Typing it at the very beggining of the script has no problem (success), what concerns me is that this may be an issue using XDIM_MEMBER further in the script, or is it neccesary to type every single member that will be used before any other command?

Thanx again

Velázquez

[Former Member]

Hi,

I tried the logic you suggested but nothing happens... once typing :

*XDIM_MEMBER CUENTA = 59IM90000

*WHEN GET(CUENTA="59IM90000")

*IS <0

*REC(FACTOR=0,CUENTA="59IM90000")

*ENDWHEN

*COMMIT

After checking the log for the final account 59IM90000 (where the data is going to be saved) this account is non existent in the log.

What else can i do? I need help asap.

Thanx again.

Velázquez

[Former Member]

Hi Velázquez,

If you have suspicions on XDIM_MEMBER statement, try using

*XDIM_MEMBERSET statement. This can bring multiple members into scope.

Also, are you using this XDIM statement within any WHEN statement?

Please check if any WHEN statement is not closed with the ENDWHEN statement, prior to this XDIM statement.

Could you please post the script you are trying to write, so that we can help?

Karthik AJ

[Former Member]

Hi Velázquez,

If there are no negative values in those accounts, the logic will not create any new records. So the log might be empty. Please check if you have any negative values or not.

Hope this helps.

Karthik AJ

[Former Member]

Karthik,

Here is the code, thanx in advance !!

*WHEN TIEMPO

*IS 2012.ENE,2012.FEB,2012.MAR,2012.ABR,2012.MAY,2012.JUN,2012.JUL,2012.AGO,2012.SEP,2012.OCT,2012.NOV,2012.DIC

*WHEN CUENTA.IVAACRED

*IS "Y"

*REC(EXPRESSION=%VALUE% * GET(METRICAS="IND.IVA",MONEDA="NAMON",VERSION="NAVER",CUENTA="NACTA",AREA="NAAREA"),CUENTA="13010A")

*ENDWHEN

*WHEN CUENTA

*IS 64600

*REC(EXPRESSION= %VALUE% * GET(METRICAS="IND.ALIMENTACION",MONEDA="NAMON",VERSION="NAVER",CUENTA="NACTA",AREA="NAAREA"),CUENTA="64600A")

*ENDWHEN

*WHEN CUENTA.SCUTI

*IS "Y"

*REC(EXPRESSION=%VALUE% * GET(METRICAS="IND.FACTOR.ISR",MONEDA="NAMON",VERSION="NAVER",CUENTA="NACTA",AREA="NAAREA")* GET(METRICAS="IND.ISR",MONEDA="NAMON",VERSION="NAVER",CUENTA="NACTA",AREA="NAAREA"),CUENTA="18010")

*REC(EXPRESSION=%VALUE% * GET(METRICAS="IND.ISR",MONEDA="NAMON",VERSION="NAVER",CUENTA="NACTA",AREA="NAAREA"),CUENTA="26000")

*ENDWHEN

*WHEN CUENTA.IVATRAS

*IS "Y"

*REC(EXPRESSION=%VALUE% * GET(METRICAS="IND.IVA",MONEDA="NAMON",VERSION="NAVER",CUENTA="NACTA",AREA="NAAREA"),CUENTA="26201")

*ENDWHEN

*WHEN CUENTA.SCUTI

*IS "Y"

*REC(EXPRESSION= %VALUE% * GET(METRICAS="IND.ISR",MONEDA="NAMON",VERSION="NAVER",CUENTA="NACTA",AREA="NAAREA"),CUENTA="59IM90000")

*ENDWHEN

*XDIM_MEMBER CUENTA = 59IM90000

//*WHEN SIGNEDDATA

*WHEN GET(CUENTA="59IM90000")

*IS <0

*REC(FACTOR=0,CUENTA="59IM90000")

*ENDWHEN

*COMMIT

*ENDWHEN

*COMMIT

[Former Member]

Hi,

As i said earlier, XDIM statements cannot be used within a WHEN statement. In the above code, XDIM statement is used within a WHEN statement. The WHEN statement for TIEMPO dimension was not closed with a ENDWHEN statement. For you case, the code can be wriiten without XDIM statement like;

*WHEN CUENTA

*IS 59IM90000

*WHEN GET(CUENTA="59IM90000")

*IS <0

*REC(FACTOR=0,CUENTA="59IM90000")

*ENDWHEN // to end GET statement

*ENDWHEN // to end CUENTA comparison

//then finally the TIEMPO comparison can be closed

*ENDWHEN

*COMMIT // final commit statement

I have modified only the final part of the script logic without the WHEN statement.

Hope this helps.

Karthik AJ

[Former Member]

Hi,

This script is 50% successful, since the logic that you gave me do works, however the instructions for negative operations result in non sense data. This is the logic that you suggested, and I already tried different ways to obtain zero, like:

-

-

---

IS <0

*REC(FACTOR=0,CUENTA="59IM90000")

-

-

---

*REC(EXPRESSION= 0,CUENTA="59IM90000")

-

-

---

*REC(EXPRESSION= GET(CUENTA="59IM90000") * 0, CUENTA="59IM90000")

-

-

---

Also tried dividing the same amount obtained and susbtracting 1 ( 5 / 5 = 1 -> 1-1 = 0) , etc. but the account 59im90000 at the end always gets the amount 210.30, i really dont know why.

For example, this code works for values >0 and <0 , like:

*ELSE
*REC(FACTOR=5,CUENTA="59IM90000")
Result: 5 * 5 = 25 logic OK

However, multiplying by zero or using any other operation that intends to obtain zero fails (resulting in 210.30) just like the above REC instructions.

What i finally tried and succesfully got 0 is:

*WHEN TIEMPO
*IS 2012.ENE,2012.FEB,2012.MAR,2012.ABR,2012.MAY,2012.JUN,2012.JUL,2012.AGO,2012.SEP,2012.OCT,2012.NOV,2012.DIC

*WHEN CUENTA
*IS 59IM90000
*WHEN GET(CUENTA="59IM90000")
*IS > 0

*REC(EXPRESSION=%VALUE%, CUENTA="59IM90000")

*ELSE

*REC(EXPRESSION= GET((CUENTA="29GO1300") * 0) - 210.30,CUENTA="59IM90000")

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

As you can see... this is not even a workaround, what do you suggest?

Doing some research, i thought that modifying the parameter "limit of diference" could help but the code above works (not for obtaining 0...) and proves this "theory" is wrong.

Thanks again,

Velázquez

[Former Member]

Hi Velázquez,

The code can be as simple as this. Could you please try this?

*WHEN TIEMPO
*IS 2012.ENE,2012.FEB,2012.MAR,2012.ABR,2012.MAY,2012.JUN,2012.JUL,2012.AGO,2012.SEP,2012.OCT,2012.NOV,2012.DIC

  *WHEN CUENTA
  *IS 59IM90000
    *WHEN GET(CUENTA="59IM90000")
    *IS > 0
      *REC(FACTOR=1)
    *ELSE
      *REC(FACTOR=0)
    *ENDWHEN
  *ENDWHEN

*ENDWHEN
*COMMIT

[Former Member]

Hi,

I tried again with the code provided... and the problem remains. I already did a "clear data", send new data and the values at the end are different than zero...

Is there a possibility that my data is "corrupted" or something? I bring this up because these modifications are being made in a new server and maybe the database was not "restored correctly" or something like it.

Thanks again, really appreciate it.

Velázquez

[Former Member]

Hi Velázquez,

Can you please use a *COMMIT statement before your piece of code that does the clearing for the "59IM90000" member in CUENTA dimension? Like;

*COMMIT

*WHEN TIEMPO
*IS 2012.ENE,2012.FEB,2012.MAR,2012.ABR,2012.MAY,2012.JUN,2012.JUL,2012.AGO,2012.SEP,2012.OCT,2012.NOV,2012.DIC
 
  *WHEN CUENTA
  *IS 59IM90000
    *WHEN GET(CUENTA="59IM90000")
    *IS > 0
      *REC(FACTOR=1)
    *ELSE
      *REC(FACTOR=0)
    *ENDWHEN
  *ENDWHEN
 
*ENDWHEN
*COMMIT

If this works, then I can explain why it was not zeroing out before.

Hope this helps.

Karthik AJ

[Former Member]

Hi Karthik,

The logic you provided do works for obtaining zero and we applied it to base members...

The problem identified is that after rolling up, the parent values respond different to the logic by doing the following...

There are 2 accounts: Income, Costs

4 main areas (parents) : administration, sales, outsourcing, consultant

ISR: EBITDA * 30%

If ISR (account) is negative, this value should change to zero.

OUTSOURCING (Parent) : s_outsourcing (Son --> only for income) , O_Gastos (Son --> only for costs)

____________________________________

Area= s_outsourcing Area= O_Gastos Outsourcing

Income = 100,000 + Income = 0 = Income = 100,000

Costs = 0 + Costs = -50,00 = Costs = -50,000

EBITDA = 100,000 + EBITDA = -50,000 = EBITDA = 50,000

ISR(30%) = 30,000 + ISR = 0 = ISR = 30,000 --> WRONG!!!

Correct answer = EBITDA * 30% = 15,000

The ideal situation would be to perform this calc on a parent level (outsourcing), because the result for base members provide an inconsistency after rolling up.

Many thanks, really appreciate it.

Velázquez

[Former Member]

Hi Velázquez,

I do not understand the hierarchy and the calculation logic you have tried to explain. Could you please explain again? Any calculation can be done in the base-level members itself. If not, we can use MDX formulae in the dimension members.

The hierarchy i could understand is;

Outsourcing

|

|----

-

---

s_outsourcing

|

|----

-

---

O_Gastos

I do not understand how other members are in the hierarchy.

Karthik AJ

[Former Member]

Hi Karthik,

The hierarchy you understood is correct

OUTSOURCING (Parent)

-

---

s_outsourcing (Son)

-

---

O_Gastos (Son)

This is an example of our report, what we look for in our script...

We want the calc to be applied to the parent values, instead of doing the roll up.

-

---

Area= s_outsourcing

Income = 100,000

Costs = 0

EBITDA = 100,000

ISR (30%) = 30,000

-

---

Area= O_Gastos

Income = 0

Costs = -50,000

EBITDA = -50,000

ISR = 0

-

---

Outsourcing

Income = 100,000

Costs = -50,000

EBITDA = 50,000

ISR = 30,000 --> WRONG!!!

Correct answer = EBITDA * 30% = 15,000

[Former Member]

Hi Karthik

I have a question related to this, is there a way to call just the members of certain "HLEVEL" for a calc?

For example in this case to perform the calc for those members which are in the 3rd level, HLEVEL=3.

Thanks a lot!

Nidia Olguí

[Former Member]

Hi Velázquez,

For this calculation, you can use an MDX formula. You can define the calculation in the Formula property of the ISR member in the dimension member sheet.

You may try using the below formula in the dimension sheet;

.[EBITDA]*0.3

Please use the dimension name instead of .

Hope this helps.

Karthik AJ

[Former Member]

Hi Nidia,

I would suggest you to post a new thread.

For your requirement, you may try defining a property and use a SELECT statement in the script. However, in script logic, you can use only the base-level members in the calculation.

Or

Can you try this?

*SELECT(%H3_MEMBER_LIST%,"ID","DIMENSION","ID in (select distinct ID1 from dimDIMENSION where HLEVEL1=3 and calc='N')")

You may use this member list, where you want.

Please let me know if it works.

Karthik AJ

[Former Member]

Velázquez,

For your second question, you must filter all those dimension members (otherthan cuenta being 130101A) whose values are less than 0 using the below instruction and store it in *XDIM_GETINPUTSET.

For ex: If you know the exact member of 1(Cuenta) out of 2 dimensions(Entity & Cuenta), and you want to filter the 2nd dimension to only those which has a value less than 0, you might use something as below:

*XDIM_GETMEMBERSET ENTITY

*XDIM_MEMBERSET CUENTA = 130101A

*CRITERIA SIGNEDDATA <0

*ENDXDIM

*GO

This will give you all the entities which has less than 0. Then execute this

*WHEN ENTITY

*IS *

*REC(...) // multiply with Factor = 0

*ENDWHEN ENTITY

*COMMIT

[Former Member]

Hi,

Thanx for your response, i tried to type this as you suggested but an error is shown and does not validate the code:

- Cuenta = dim account

*xdim_getinputset cuenta --- this hole row is typed in red

*xdim_memberset cuenta = 13010A -

---

the word cuenta is typed in red

*criteria signeddata <0

*endxdim

*go

*when cuenta

*is 13010A

*rec(expression= %value% * 0, cuenta="13010A")

*endwhen

*commit

Regards

[Former Member]

What does your error message say?

[Former Member]

Hi Rocky,

It says Syntax error... however if the sentece is typed at the top of the code (1° line) not in the half of it, it seems to be ok.

If this error repeats, how am i going to be able to use this type of commands in the rest of the script?

Thanx