How can REC allocate to next periods?

[Former Member]

Hi Experts,

How can a REC statement spread values to next periods, i.e. a year forward?

Basically, it's unknown in which month the end user will plan to expect new customers, and depending on the type of customer, the expected revenue increase is going to spread seasonally to the next months going forward.

I also don't see how this can be parametrized in RUNALLOCATION, nor with account based business rules, nor with LOOKUPS.

For example, I want below example to work somehow, but there seems to be no good statement I can use for the red part below to get the current TIME member and offset it using TMVL. Using %TIME_SET% there is not an option either.

*XDIM_MEMBERSET TIME = <all>

*WHEN ACCOUNT

*IS NEW_CUSTOMERS

*REC(FACTOR=1/12,ACCOUNT=NEW_SALES_REVENUE,TIME=TMVL(1,TIME.ID))

*REC(FACTOR=1/12,ACCOUNT=NEW_SALES_REVENUE,TIME=TMVL(2,TIME.ID))

*REC(FACTOR=1/12,ACCOUNT=NEW_SALES_REVENUE,TIME=TMVL(3,TIME.ID))

...

*ENDWHEN

If you have any ideas besides ABAP coding, let me know.

[former_member186338]

Hi Jef,

Can you clearly explain your requirements (not some not working script)?

Use Excel to create sample incoming data and the desired result! Read

question number 4? and other...

Vadim

[Former Member]

Hi Vadim,

Please find herewith a much simplified sample in excel.

Yellow cells = user input / variables
Green cells = desired output
Grey cells = (intermediate calculation results fyi)

1. BPC 10.0 730 SP16
2. ABAP calc engine
3. dimensions involved: ACCOUNT, TIME, CATEGORY
4. calculation by DM package
5. description above
6. script not designed yet, sample above.
7. users only select CATEGORY dimension, rest should calculate on it's own. users can plan on any TIME member... and data can be sparse... hence I was thinking REC is best option.
8. /

9. green cells is the output

[former_member186338]

Hi Jef,

1. "4. calculation by DM package" and "users only select CATEGORY dimension, rest should calculate on it's own." - huge amount of records will be recalculated including all existing?

2. Recalculation sequence of the months will be an issue... Revenue have to be zeroed then you have to calculate all time periods in the increasing order. And you have to add new revenue value to the existing...

If you really want to implement the described functionality - think about badi (and with write back you will be able to recalculate only changed data)!

Vadim

[Former Member]

1. Yes it's quite an amount, but nog 'very huge'... a few 10 thousands records...

2. I want to prevent BADI's for business flexibility and as well any calculation on manual save actions (like write-back badi) because the source data will be a mix of inputted and loaded records.
The first action could indeed be a clear script (zero-ing the previous calculation results).

Let me simplify my question on script logic.

What if I only want to push the amount of new customers forward in time periods, based on contract type like this:

I don't see a way in script logic to just push the 4 yellow cell values 3 months (or 6 months) forward...
The *REC and TMVL statements are not capable to look at the current TIME member it is looping from the *WHEN statement.

Only with a *FOR / *NEXT loop you can do this... but this method is not very performant when the scenario gets bigger and more complicated... i.e. with a 10Y forecast.

*XDIM_MEMBERSET ACCOUNT = NEW_CUSTOMERS_3M

*XDIM_MEMBERSET TIME AS %ALL_MONTHS% = <all>

*FOR %CURTIME% = %ALL_MONTHS%
*WHEN ACCOUNT

*IS NEW_CUSTOMERS_3M

*REC(EXPRESSION=%VALUE%, ACCOUNT=NEW_CUST_TOT)

*REC(EXPRESSION=%VALUE%, ACCOUNT=NEW_CUST_TOT,TIME=TMVL(1,%CURTIME%))

*REC(EXPRESSION=%VALUE%, ACCOUNT=NEW_CUST_TOT,TIME=TMVL(2,%CURTIME%))

*ENDWHEN

*NEXT

With this post I just wanted to check if ayone knows something smart to get around this contrstruct in script logic (besides BADI's).

[former_member186338]

"Only with a *FOR / *NEXT loop you can do this... but this method is not very performant when the scenario gets bigger and more complicated... i.e. with a 10Y forecast." - absolutely correct!

Another option is to create number of properties in the TIME dimension like:

MNTHPLUS1

MNTHPLUS2

MNTHPLUS3

MNTHPLUS4

...

MNTHPLUS6

And maintain all properties for all base members of TIME dimension.

Then you will be able to write like:

*REC(EXPRESSION=%VALUE%*something,TIME=TIME.MNTHPLUS1)

...

But the sequence of writes will be an issue due to overlapped periods!

Think about badi!

Vadim

[former_member186338]

Ok, it's possible to do like:

*SELECT(%MNTHS%,[ID],TIME,[CALC]=N) //all base months

*XDIM_MEMBERSET TIME=%MNTHS%

//Clear NEW_CUST_TOT

*XDIM_MEMBERSET ACCOUNT = NEW_CUST_TOT

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=0)

*ENDWHEN

//Write to next 3 months

*XDIM_MEMBERSET ACCOUNT = NEW_CUSTOMERS_3M

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%*something,ACCOUNT=NEW_CUST_TOT,TIME=TIME.MNTHPLUS1)

*REC(EXPRESSION=%VALUE%*something,ACCOUNT=NEW_CUST_TOT,TIME=TIME.MNTHPLUS2)

*REC(EXPRESSION=%VALUE%*something,ACCOUNT=NEW_CUST_TOT,TIME=TIME.MNTHPLUS3)

*ENDWHEN

Overlapping will be OK due to WHEN/ENDWHEN accumulation!

Vadim

[Former Member]

Yes, already tought of that... but for a 10Y forecast, this means adding and administrating 120 new properties in the TIME dimension that is shared with 9 other BPC models.

Other way that could work is using account based business rules CALC_ACCOUNT and put "+1" or "+2" in the "Period" mapping column... but then again this might not accumulate properly.

[former_member186338]

"but for a 10Y forecast, this means adding and administrating 120 new properties in the TIME dimension that is shared with 9 other BPC models." - 120 new properties???? only 6!

May be you mean 12months*10years*6properties=720 property values to maintain? But this is not a big issue!

Business rules will definitely have an issue with accumulation.

Vadim

[Former Member]

If a customer goes for a 10 year contract, that is possibly 120 months forward = 120 properties.

120 properties x 120 months to maintain = 14,400 property values to populate in BPC 10.0 Admin interface (probably even more)... awwch.

[former_member186338]

Sorry Jef,

But always specify your requirements: 10Y forecast <> 10 year contract

Also it's the question how many account members you will have:

1 month - 10*12=120 month - 120 members

Looks like you are trying to achieve in BPC something that can't be interpreted in cube logic You are trying to simulate ERP transactions...

Vadim

[Former Member]

To me this is not ERP like... I'm just planning 'amount' of new customers by some small set of contract types. There would only be 13 account members... one for each contract type (3M, 6M, 1Y, 2Y,...,10Y) and one for their generated revenue.

[former_member186338]

You will not have 18 month contracts? Are you sure?

Anyway, as I already explained you at the beginning - use badi. The requirements you want to implement are not supported without ABAP programming.

Vadim

[Former Member]

Thanks for confirming.

Please vote here if you also would like to see this to be possible in standard script logic.

https://ideas.sap.com/D27406

[former_member186338]

Sorry, but looks like none of my posts were useful for you...

And it's strange to ask the new functionality from TMVL - it's working with text strings on the preprocessor level (not during WHEN/ENDWHEN loop execution).

Vadim

[Former Member]

I might have gotten onto something using plain MDX...

will of course need to test performance of this stuff.

*SELECT(%MNTHS%,[ID],TIME,[CALC]=N)

*XDIM_MEMBERSET ACCOUNT = NEW_CUSTOMERS_3M

*XDIM_MEMBERSET CAGETORY = PLAN1

*XDIM_MEMBERSET TIME = %ALL_MONTHS%

*BEGIN

[ACCOUNT].[#TOT_SALES_REV] =

    ([ACCOUNT].[NEW_CUSTOMERS_3M]) * ([ACCOUNT].[SRATE])
  +([ACCOUNT].[NEW_CUSTOMERS_3M],[TIME].PREVMEMBER) * ([ACCOUNT].[SRATE])

  +([ACCOUNT].[NEW_CUSTOMERS_3M],[TIME].PREVMEMBER.PREVMEMBER) * ([ACCOUNT].[SRATE])

*END

[former_member186338]

Slow! Very slow!