Variable Reading in Rec Statement Not Working(Error as RUN_LOGIC:Unknown Dimension Name in Keyword)

[former_member672998]

Hi Team,

We are BPC 11 standard, and wanted to simple calculation using script logic as below but getting error "RUN_LOGIC:Unknown Dimension Name in Keyword: "2019:"

Script:

*SELECT(%NPLC%,"[ID]",ACCOUNT,"[LOGIC_RULE]='NONPLCTRL'")

//%NPLC% : This variable will hold 2 accounts as 630 and 640

//%CUR-MTH% : will hold the current quarter input period, in current scenario its 2020.03

*XDIM_MEMBERSET CURRENCY = USD

*XDIM_MEMBERSET CATEGORY = ACTUAL,FORECAST

*XDIM_MEMBERSET ACCOUNT = %NPLC%,400

*XDIM_MEMBERSET DATASRCE = INPUT

*XDIM_MEMBERSET TIME= BAS(%PREVIOUS_QTR%),%CUR-MTH%

*FOR %NPLSC% = %NPLC%

*WHEN CATEGORY

*IS ACTUAL

*WHEN DATASRCE

*IS INPUT

*WHEN TIME

*IS BAS(%PREVIOUS_QTR%)

*REC(EXPRESSION=((%VALUE%)/([ACCOUNT].[400],[CATEGORY].[ACTUAL],[TIME].[BAS(%PREVIOUS_QTR%)],[DATASRCE].[INPUT])),CATEGORY=FORECAST,TIME=%CUR-MTH%,DATASRCE=CALC)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*NEXT

*COMMIT

Issue is in reading the BAS(%PREVIOUS_QTR%) inside the rec statement.

Please assist.

[former_member186338]

Sorry, but full script is not provided and no other info:

https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

[former_member672998]

BPC Version :

BPC4HANA 100 0005 SAPK-10005INBPC4HANA BPC/4HANA

Engine : JAVASCRIPT

Model : Planning model

Sricpt purpose : To calculate current quarter Forecast data using previous quarter Actual data.

Below Full script :

*SELECT(%CURRENT_FORE%,"[ID]",CATEGORY,"[IS_CURRENT]='Y'")

*SELECT (%CURRENT_QTR% , "[STARTQTR]",CATEGORY,"[IS_CURRENT]='Y'")

*SELECT (%PREVIOUS_QTR% , "[PREVQTR]",CATEGORY,"[IS_CURRENT]='Y'")

*SELECT(%CUR_QTR_MTH%,"[MONTHNUM]",TIME,"[ID]='%CURRENT_QTR%'")

*SELECT(%CURRENT_YEAR%,"[YEAR]",TIME,"[ID]='%CURRENT_QTR%'")

*SELECT(%CUR-MTH%,"[ID]",TIME,"[MONTHNUM]='%CUR_QTR_MTH%'" AND "[YEAR]='%CURRENT_YEAR%'" AND "[CALC]='N'")

*SELECT(%NPLC%,"[ID]",ACCOUNT,"[RULE]='NRL'")

*XDIM_MEMBERSET CURRENCY = USD

*XDIM_MEMBERSET CATEGORY = ACTUAL,FORECAST

*XDIM_MEMBERSET ACCOUNT = %NPLC%,400

*XDIM_MEMBERSET DATASRCE = INPUT

*XDIM_MEMBERSET TIME AS %LST_QTR% = BAS(%PREVIOUS_QTR%)

*XDIM_ADDMEMBERSET TIME= BAS(%PREVIOUS_QTR%),%CUR-MTH%

*FOR %NPLSC% = %NPLC%

*WHEN CATEGORY

*IS ACTUAL

*WHEN DATASRCE

*IS INPUT

*WHEN TIME

*IS BAS(%PREVIOUS_QTR%)

*REC(EXPRESSION=((%VALUE%)/([ACCOUNT].[400],[CATEGORY].[ACTUAL],[TIME].[%LST_QTR%],[DATASRCE].[INPUT])),CATEGORY=FORECAST,TIME=%CUR-MTH%,DATASRCE=CALC)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*NEXT

*COMMIT

Executed from Data manager, we are passing the CAtegory: FORECAST but anyway the script is controlled through master data of Category dimension.

Error we are getting : RUN_LOGIC:Unknown Dimension Name in Keyword: "2019:

UJKT Error : UJK_VALIDATION_EXCEPTION:Unknown Dimension Name in Keyword: "2019:

[former_member186338 · Accepted solution]

Script:

*SELECT(%CURRENT_FORE%,[ID],CATEGORY,[IS_CURRENT]=Y) 
*SELECT(%CURRENT_QTR%,[STARTQTR],CATEGORY,[IS_CURRENT]=Y) 
*SELECT(%PREVIOUS_QTR%,[PREVQTR],CATEGORY,[IS_CURRENT]=Y) 
*SELECT(%CUR_QTR_MTH%,[MONTHNUM],TIME,[ID]=%CURRENT_QTR%)
*SELECT(%CURRENT_YEAR%,[YEAR],TIME,[ID]=%CURRENT_QTR%)
*XDIM_MEMBERSET TIME=%CURRENT_YEAR%.01 //Assuming that months are 01,02,..
*SELECT(%CUR-MTH%,[ID],TIME,[MONTHNUM]=%CUR_QTR_MTH% AND [YEAR]=%CURRENT_YEAR% AND [CALC]=N)

*XDIM_MEMBERSET TIME=%CUR-MTH% //2020.03
*XDIM_MEMBERSET CURRENCY = USD
*XDIM_MEMBERSET CATEGORY=%CURRENT_FORE% //FORECAST 
*XDIM_MEMBERSET DATASRCE = FT
*XDIM_MEMBERSET ACCOUNT = 630 //%NPLC%

*WHEN ACCOUNT
*IS *
*REC(EXPRESSION=%VALUE%*([TIME].[%PREVIOUS_QTR%],[DATASRCE].[INPUT],[CATEGORY].[ACTUAL])/(([ACCOUNT].[400],[TIME].[%PREVIOUS_QTR%],[DATASRCE].[INPUT],[CATEGORY].[ACTUAL])+([ACCOUNT].[401],[TIME].[%PREVIOUS_QTR%],[DATASRCE].[INPUT],[CATEGORY].[ACTUAL])+([ACCOUNT].[402],[TIME].[%PREVIOUS_QTR%],[DATASRCE].[INPUT],[CATEGORY].[ACTUAL])),DATASRCE = CALC)
*ENDWHEN

[former_member672998 · Accepted solution]

Sorry, I misinterpreted the formula...correct formula is as below:

Factor is correct as previous but the multiplier should be added with current quarter 2 months actual as well!!

[former_member186338 · Accepted solution]

nileshjsap

Sorry, but now the logic is again not clear!

What do you mean by SUM(F5:H7)??? It's a rectangle, not a column or row...

Please provide a detailed explanation from the business point of view:

What do you mean by accounts, how 630 is related to 400,401,...

What is the meaning of different DATASRCE

...

[former_member672998 · Accepted solution]

Thanks Vadim!! We will go ahead with the current solution and enhance it further!! thanks for your help!!

[former_member186338 · Accepted solution]

nileshjsap

In general, the approach has to be: detailed business logic first, then script!

[former_member672998]

This is what we are trying to achieve. FYI, 400 and 401 are only basemembers ofa parent 40 whereas 402 is separate member.

[former_member186338]

Please confirm the logic:

We have ACCOUNT 630 with FORECAST value in DATASRCE FT

We want to calculate ACCOUNT 630 FORECAST value in DATASRCE CALC

Factor: for prev quarter total ACTUAL DATASRCE INPUT: [630]/([400]+[401]+[402])

[former_member672998]

Yes..this is the logic!!

[former_member186338]

Error found:

*XDIM_MEMBERSET TIME AS %LST_QTR% = BAS(%PREVIOUS_QTR%)
//%LST_QTR% = 2019.10,2019.11,2019.12
//Absolutely incorrect to use multiple members in REC
*REC(EXPRESSION=((%VALUE%)/([ACCOUNT].[400],[CATEGORY].[ACTUAL],[TIME].[%LST_QTR%],[DATASRCE].[INPUT])),CATEGORY=FORECAST,TIME=%CUR-MTH%,DATASRCE=CALC)
//[TIME].[%LST_QTR%] - ERROR!

[former_member186338]

P.S. The required calculation logic IS NOT EXPLAINED!!! Incorrect script is not an explanation. Please read again:

https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

[former_member672998]

Fair enough,

Let say if I pass below Rec statement, will the [ACCOUNT].[400] will be picked up from the same intersection as all values in When statements?

In below case I want other intersection for the [ACCOUNT].[400] to be picked up same as %VALUE%

 *WHEN CATEGORY
*IS ACTUAL
*WHEN DATASRCE
*IS INPUT
*WHEN TIME
*IS BAS(%PREVIOUS_QTR%)
*REC(EXPRESSION=((%VALUE%)/([ACCOUNT].[400])),CATEGORY=FORECAST,TIME=%CUR-MTH%,DATASRCE=CALC)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN

[former_member186338]

nileshjsap

Sorry again, but:

"Let say if I pass below Rec statement, will the [ACCOUNT].[400] will be picked up from the same intersection as all values in When statements?

In below case I want other intersection for the [ACCOUNT].[400] to be picked up same as %VALUE%"

Is not a logic explanation!!!!

Provide a clear data sample and expected results as a table in Excel. Post the screenshot.

[former_member186338]

In UJKT please run the following and post results (EXECUTE):

*SELECT(%CURRENT_FORE%,[ID],CATEGORY,[IS_CURRENT]=Y) 
*XDIM_MEMBERSET CATEGORY=%CURRENT_FORE%
*SELECT(%CURRENT_QTR%,[STARTQTR],CATEGORY,[IS_CURRENT]=Y) 
*XDIM_MEMBERSET TIME=%CURRENT_QTR%
*SELECT(%PREVIOUS_QTR%,[PREVQTR],CATEGORY,[IS_CURRENT]=Y) 
*XDIM_MEMBERSET TIME=%PREVIOUS_QTR%
*SELECT(%CUR_QTR_MTH%,[MONTHNUM],TIME,[ID]=%CURRENT_QTR%)

*SELECT(%CURRENT_YEAR%,[YEAR],TIME,[ID]=%CURRENT_QTR%)
*XDIM_MEMBERSET TIME=%CURRENT_YEAR%.01 //Assuming that months are 01,02,..
*SELECT(%CUR-MTH%,[ID],TIME,[MONTHNUM]=%CUR_QTR_MTH% AND [YEAR]=%CURRENT_YEAR% AND [CALC]=N)
*XDIM_MEMBERSET TIME=%CUR-MTH%

I have removed a lot of useless " ' from the script

[former_member672998]

LGX:
*XDIM_MEMBERSET CATEGORY = FORECAST
*XDIM_MEMBERSET TIME=2020.Q1
*XDIM_MEMBERSET TIME=2019.Q4
*XDIM_MEMBERSET TIME=2020.01
*XDIM_MEMBERSET TIME=2020.03

[former_member186338]

nileshjsap

OK, UJKT result is fine! Read my second answer. The error is described in this answer.