Help with Script logic

[Former Member]

Hi,

We are working on SAP BPC 10.1 NW version on HANA. I need your help regarding script logic. I am executing the following code which get the Year from the Category dimension and then uses it in script logic to get the sum of sales 3 years prior and 2 years prior. It also gets the sum of sales for a particular period specified in the For loop 3 years and 2 years prior. When I executes the following code, it multiplies the value (sales) by 28 times sometimes 25 times etc.Please help!!! Kindly find attached the log files.

*SELECT(%YR%,"[YEAR]",CATEGORY,"[ID] = BUDGET")

*XDIM_MEMBERSET CATEGORY = ACTUAL

// *XDIM_MEMBERSET ACCOUNT = BAS(FS0132), BAS(FS0133)

*XDIM_MEMBERSET ACCOUNT = 400000

*XDIM_MEMBERSET PROFIT_CENTER = PC_101004, PC_101001

*XDIM_MEMBERSET DATATYPE=DT_CURRENCY

*XDIM_MEMBERSET COST_CENTER=CC_NONE

*XDIM_MEMBERSET INTERCO=I_NONE

*XDIM_MEMBERSET MEMBER_VENDOR AS %MEM% = BAS(MEMBER)

*WHEN ACCOUNT

  *IS 400000

*WHEN PROFIT_CENTER

*IS PC_101004

*WHEN MEMBER_VENDOR

*IS %MEM%

  *FOR %MYPER% = %YR%.01,%YR%.02,%YR%.03,%YR%.04,%YR%.05,%YR%.06,%YR%.07,%YR%.08,%YR%.09,%YR%.10,%YR%.11,%YR%.12

  *REC(EXPRESSION = ([TIME].[TMVL(-36,%YR%.01)] + [TIME].[TMVL(-36,%YR%.02)] + [TIME].[TMVL(-36,%YR%.03)] + [TIME].[TMVL(-36,%YR%.04)] + [TIME].[TMVL(-36,%YR%.05)] + [TIME].[TMVL(-36,%YR%.06)] + [TIME].[TMVL(-36,%YR%.07)] + [TIME].[TMVL(-36,%YR%.08)] + [TIME].[TMVL(-36,%YR%.09)] + [TIME].[TMVL(-36,%YR%.10)] + [TIME].[TMVL(-36,%YR%.11)] + [TIME].[TMVL(-36,%YR%.12)] + [TIME].[TMVL(-24,%YR%.01)] + [TIME].[TMVL(-24,%YR%.02)] + [TIME].[TMVL(-24,%YR%.03)] + [TIME].[TMVL(-24,%YR%.04)] + [TIME].[TMVL(-24,%YR%.05)] + [TIME].[TMVL(-24,%YR%.06)] + [TIME].[TMVL(-24,%YR%.07)] + [TIME].[TMVL(-24,%YR%.08)] + [TIME].[TMVL(-24,%YR%.09)] + [TIME].[TMVL(-24,%YR%.10)] + [TIME].[TMVL(-24,%YR%.11)] + [TIME].[TMVL(-24,%YR%.12)]), TIME = TMVL(0,%MYPER%), ACCOUNT = "PRIOR_YR_SALES", MEMBER_VENDOR = "MV_NONE", DATATYPE = "DT_CURRENCY", CATEGORY = "BUDGET", AUDITTRAIL = "SEASONALITY_ADJ")

  *REC(EXPRESSION = ([TIME].[TMVL(-36,%MYPER%)] + [TIME].[TMVL(-24,%MYPER%)]), TIME = TMVL(0,%MYPER%), ACCOUNT = "TWO_PRIOR_PERIOD_SALES", DATATYPE = "DT_CURRENCY", CATEGORY = "BUDGET", AUDITTRAIL = "SEASONALITY_ADJ", MEMBER_VENDOR = "MV_NONE")

  *NEXT

  *ENDWHEN

  *ENDWHEN

  *ENDWHEN

*COMMIT

LGX.txt.zip

[former_member186338 · Accepted solution]

Hi Glorida,

First of all: "to get the sum of sales 3 years prior and 2 years prior" - not clear, what do you exactly mean?

Looking on your code I am unable to understand what do you want to sum up and where to store result. Please show excel table with the desired calculation logic.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thanks for replying.

An example is as follows.

Lets say the Year property maintained by the user for the Category member "BUDGET" is 2016.

The first REC statement takes the Sum of the Sales for the entire year of 2013 and 2014 and puts it in the following. It stores the total sales to every month of 2016 and the For loop is doing that.

TIME= 2016.01 to 2016.12 ACCOUNT = "PRIOR_YR_SALES", MEMBER_VENDOR = "MV_NONE", DATATYPE = "DT_CURRENCY", CATEGORY = "BUDGET", AUDITTRAIL = "SEASONALITY_ADJ"

The second REC statement takes the Sum of the sum of the Sales in the same 2 periods of the above years for example say 2013.01 + 2014.01 and puts it in 2016.01; 2013.02 + 2014.02 and puts it in 2016.02. Here the For loop is also doing that.The results are also stored at the following location.

ACCOUNT = "TWO_PRIOR_PERIOD_SALES", DATATYPE = "DT_CURRENCY", CATEGORY = "BUDGET", AUDITTRAIL = "SEASONALITY_ADJ", MEMBER_VENDOR = "MV_NONE"

Sincerely,

Glorida

[former_member186338 · Accepted solution]

Sorry, Glorida!

If you want to get help, please: "Please show excel table with the desired calculation logic."

Impossible to understand where the result is stored:

"The first REC statement takes the Sum of the Sales for the entire year of 2013 and 2014 and puts it in the following. It stores the total sales to every month of 2016 and the For loop is doing that."

In the script I can see that TIME dimension is not scoped at all - impossible to predict the logic.

Please, show sample figures!

Vadim

P.S. And please read this document:

[Former Member · Accepted solution]

Hi Vadim,

Thanks for letting me know. Please find attached the example data set and logic - what it should be.

Thanks again,

Sales for XYZ Profit Center

for example: The "TWO_PRIOR_PERIOD_SALES" for 2016.01 and for profit center XYZ should have been Sum of Sales in 2013.01 of XYZ + Sum of sales in 2014.01 of XYZ

Similarly The "TWO_PRIOR_PERIOD_SALES" for 2016.02 and for Profit Center XYZ should have been Sum of Sales in 2013.02 of XYZ + Sum of sales in 2014.02 of XYZ

The "Prior_Year_Sales" in every period of 2016 for XYZ should have been Total sales of 2013 for XYZ + Total sales of 2014 for XYZ 

Sample Data set:

[former_member186338 · Accepted solution]

Just to confirm:

Prior_Year_Sales in every period of 2016 for XYZ should have been Total sales of 2013 for XYZ + Total sales of 2014 for XYZ

Calculate PRIOR_YR_SALES like:

*XDIM_MEMBERSET TIME=TMVL(-36,%YR%.01),...,TMVL(-36,%YR%.12),TMVL(-24,%YR%.01),...,TMVL(-24,%YR%.12)

*WHEN ACCOUNT

*IS *

*FOR %MYPER% = %YR%.01,%YR%.02,%YR%.03,%YR%.04,%YR%.05,%YR%.06,%YR%.07,%YR%.08,%YR%.09,%YR%.10,%YR%.11,%YR%.12

*REC(EXPRESSION=%VALUE%,TIME = %MYPER%, ACCOUNT = "PRIOR_YR_SALES", MEMBER_VENDOR = "MV_NONE", DATATYPE = "DT_CURRENCY", CATEGORY = "BUDGET", AUDITTRAIL = "SEASONALITY_ADJ")

*NEXT

*ENDWHEN

Vadim

[Former Member · Accepted solution]

Thanks Vadim!!! The Prior_Year_Sales is working perfectly.

But how do I get the Two_Prior_Period_Sales working. I tried but it is still giving the same errors.

Can you please tell me where am I going wrong? What was the problem with my old code? Do I have ot always scope for time by using XDIM_MEMBERSET when I was using/restricting it in the REC statement of my previous code?

Sincerely,

Glorida

[former_member186338 · Accepted solution]

Same way:

*FOR %P%=01,02,03,04,05,06,07,08,09,10,11,12

*XDIM_MEMBERSET TIME=TMVL(-36,%YR%.%P%),TMVL(-24,%YR%.%P%)

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION = %VALUE%, TIME =%YR%.%P%, ACCOUNT = "TWO_PRIOR_PERIOD_SALES", DATATYPE = "DT_CURRENCY", CATEGORY = "BUDGET", AUDITTRAIL = "SEASONALITY_ADJ", MEMBER_VENDOR = "MV_NONE")

*ENDWHEN

*NEXT

Vadim

[Former Member · Accepted solution]

Thanks Vadim!!! You are the man.

Just wanted to know where in my previous code, did I go wrong? Your feedback will be greatly appreciated!!!.

Sincerely,

Nicky

[former_member186338 · Accepted solution]

The general idea is simple - for a single WHEN/ENDWEN: scope all TIME members that has to be accumulated in the same destination and REC all to target TIME member (push approach).

Vadim

[Former Member · Accepted solution]

Thanks Vadim!!!

I believe the same logic can be applied to all other dimension members then, Am I right?

Sincerely,

Glorida

[former_member186338 · Accepted solution]

Yes!