Add two months data within loop - BPC Script Logic

[Former Member]

Hi All,

I am trying to write script logic to add 2 months data which needs to works in loop.

Problem is, I can calculate it using TVML when one month is selected but when multiple months are selected, it does not work.

eg.

If user run package with 4 months selected (M1, M2, M3 and M4)

Month1 Results = Month1 value

Month 2 Results = Month 1 value (calculated from above step) + Month 2 Value

Month 3 Results = Month 2 Value (calculated from above step) + Month 3 Value

Month 4 Results = Month 3 Value (calculated from above step) + Month 4 Value

Data sample.

If M1=0, M2=10, M3 = 10, M4 = 10

Expected Results:

M1 = 0, M2 = 10 (0+10), M3 = 20 (10+10), M4 = 30 (20+10)

Logic I am using now:

*select(%SEL_ID%,[ID],TIME,[ID]=%TIME_SET]%)

*WHEN CATEGORY

*IS ACTUAL

*REC(EXPRESSION=([TIME].[TMVL(0,%SEL_ID%]) + ([TIME02].[TMVL(-1,%SEL_ID%)]))

*ENDWHEN

*COMMIT

It works fine when I execute one month at a time. But when this logic is executed with 4 months selection, 0 values is generated for all 4 months (this is because the data has not been committed until all full logic is executed).

Theoretically, what needs to happen is before *ENDWHEN, data need to commit so M1 value is calculated before logic for Month 2 is executed and so.

But obviously *commit is not allowed within WHEN/ENDWHEN statement.

I have tried FOR/NEXT outside WHEN/ENDWHEN but that didn't help either.

Any suggestions?

Thanks in advance.

[former_member186338 · Accepted solution]

Hi Anil,

You have to redefine your requirements!

Why do you think that the user will select continues months???

May be you want:

1. Select start and end months - first month is JAN?

2. Calculate something like YTD?

Vadim

[former_member186338 · Accepted solution]

P.S. Please explain the business value of your logic, read

Vadim

[Former Member · Accepted solution]

Thanks Vadin for checking.

I am tying to calculate CTA on Goodwill - difference of current month end rate - end rate at the month of acquisition. Of course maintaining end rate for each acquisition is straight solution but not feasible for multiple acquisitions. So I am calculating delta CTA as difference of End rate of current month and last month, then aggregating all of these deltas to come up with total CTA (difference of current month end rate and end rate of month of acquisition) .

Running for multiple month is not prime requirement but I just want to make sure it works if user run package for multiple months.

On different note:

Although the logic is working fine it gives syntax error when I validate it.

Validation error is “Syntax error in line XX “Missing name after . operator” Error is for REC line.

*select(%SEL_ID%,[ID],TIME,[ID]=%TIME_SET]%)

*WHEN CATEGORY

*IS ACTUAL

*REC(EXPRESSION=([TIME].[TMVL(0,%SEL_ID%]) + ([TIME02].[TMVL(-1,%SEL_ID%)]))  //this has validation issue

*ENDWHEN *COMMIT

Any idea why the validation is failing?

Thanks. Regards, Anil

[former_member186338 · Accepted solution]

1. To use logic for multiple months you have to use *FOR %T%=%TIME_SET% loop

2. *select(%SEL_ID%,[ID],TIME,[ID]=%TIME_SET]%) is absolutely useless: %SEL_ID% will be equal to %TIME_SET% (and syntax error in red)

3. Validation error is normal - variable %SEL_ID% is not assigned.

4. What do you mean by *REC(EXPRESSION=([TIME].[TMVL(0,%SEL_ID%]) + ([TIME02].[TMVL(-1,%SEL_ID%)])) - TIME02 -??? another dimension?

5. COMMIT is always useless with WHEN/ENDWHEN - remove it.

Vadim

[Former Member · Accepted solution]

Thanks Vadim, Was trying to copy logic. 1. Will give try 2. Was typing logic in forum screen as I was not able to copy from BPC for some reason. 3. Can you please clarify what do you mean here by "%SEL_ID% is not assigned"? 4. Again typo when typing in screen. it is [Time]. *REC(EXPRESSION=([TIME].[TMVL(0,%SEL_ID%]) + ([TIME].[TMVL(-1,%SEL_ID%)])) 5. Thanks. Will remove it.

[former_member186338 · Accepted solution]

"Was typing logic in forum screen as I was not able to copy from BPC for some reason" - but how???

3. When you validate script %TIME_SET% is empty, then %SEL_ID% will also be empty... simply ignore validation. And use UJKT transaction to test script logic.

4. instead of *REC(EXPRESSION=([TIME].[TMVL(0,%SEL_ID%]) + ([TIME].[TMVL(-1,%SEL_ID%)])) use:

*REC(EXPRESSION=%VALUE% + ([TIME].[TMVL(-1,%SEL_ID%)]))

Vadim

[former_member186338 · Accepted solution]

P.S. And to my mind this code is better:

*FOR %T%=%TIME_SET% // for each member selected in %TIME_SET%

*WHEN CATEGORY

*IS ACTUAL

*WHEN TIME

*IS %T%

*REC(EXPRESSION=%VALUE%) 

*IS TMVL(-1,%T%)

*REC(EXPRESSION=%VALUE%,TIME=%T%)

*ENDWHEN

*ENDWHEN

*NEXT

Do you want to run this code as a DM package? Then instead of *IS you can use *XDIM_MEMBERSET...

Vadim

[Former Member · Accepted solution]

Thanks a lot Vadim.

The last code you have suggested gave the result I was looking for.

This part of the was the key. Didn't realize you can have multiple *IS statement in When/EndWhen.

*WHEN TIME

*IS %T%

*REC(EXPRESSION=%VALUE%)

*IS TMVL(-1,%T%)

*REC(EXPRESSION=%VALUE%,TIME=%T%)

3. Thanks for info. This makes sense. Will do further test in UJKT.

4. I have used %VALUE% this while testing the code but took it out because of validation failure.

Any good resource you can suggest to learn Script Logic in depth?

Thanks once again.

Regards, Anil

[former_member186338 · Accepted solution]

4. %VALUE% is not related to validation errors!

"Any good resource you can suggest to learn Script Logic in depth?" - not a lot!

1. Help for script logic (not always up to date)

2. SAP notes (search for script logic)

3. Forum search

4. ABAP debug of script execution.

For example the document http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/e025fa8d-1c22-2e10-cd9f-c488c7eea...

provide only basic info with some mistakes...

Vadim