Multiple Time Periods Selection with Lookup

[Former Member]

Hello BPC gurus,

The below script working as expected, If I select one month (Ex 01.2018) time period and run the package, the currency conversion based on last year same month (Ex 01.2017) rate conversion. The results are wrong, If I select 12 moths periods (Ex 01.2018 to 12.2018)at a time and execute the package the currency conversion is taking place on 12 months back period(Ex 01.2017). The requirement is to check the conversion factor for previous 12 months for each period even user select multiple months. Ex : If user selects three months 01.2018, 02.2018, 03.2018 the rate conversion should based on 01.2017, 02.2017, 03.2017. I am new to Script logic and don't know how to declare and introduce FOR loop in below script to achieve desired results.

The version is BPC 10 Netweaver

Please help me.

Krishna

*SELECT(%VERSION_LIST%,FX_SOURCE_CATEGORY,VERSION,ID=%VERSION_SET%)

*LOOKUP RATE
*DIM TIME = TMVL(-12,%TIME_SET%)
*DIM R_ENTITY = "GLOBAL"
*DIM VERSION=%VERSION_LIST%
*DIM R_ACCT = SUMMARY_ACCOUNT.RATETYPE
*DIM LKPLC:INPUTCURRENCY = E_ENTITY.CURRENCY
*DIM LKPSUB:INPUTCURRENCY = E_ENTITY.SUBMISSION_CURRENCY
*ENDLOOKUP

*XDIM_MEMBERSET E_ENTITY = BAS(%E_ENTITY_SET%)
*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)

*WHEN RPTCURRENCY
*IS Subm
*WHEN E_ENTITY.SUBMISSION_CURRENCY
*IS <> ""
*WHEN SUMMARY_ACCOUNT.RATETYPE
*IS <> ""
*REC(EXPRESSION = %VALUE%*LOOKUP(LKPSUB),RPTCURRENCY = USD)
*ELSE
*REC(FACTOR=1,RPTCURRENCY = USD)
*ENDWHEN
*ENDWHEN
*ENDWHEN

[Former Member]

Hi Vadim Kalinin,

Thank you for your inputs and providing alternative solution.

At this situation, I couldn't able to go with new property creation. I would like to tweak the existing code through FOR/NEXT loop to achieve but not getting idea how to implement.

I am aware FOR/NEXT with LOOKUP is slow but we have limited data to process.

Please help me to how to add FOR/NEXT loop in my code.

Regards,

Krishna

[Former Member]

Hi Vadim Kalinin

I used FOR/NEXT loop and selected 12 periods in DM package, but its failing with error " RUN_LOGIC:Invalid lookup structure: Multi LOOKUP on same cube not allowed". I thought FOR loop will run period by period if user selects 12 periods, but it's not working like that.

The code is successful if i select one month in DM package.

Please help me.

*SELECT(%VERSION_LIST%,FX_SOURCE_CATEGORY,VERSION,ID=%VERSION_SET%)
*SELECT(%TIME_LIST%,ID,TIME,ID=%TIME_SET%)

*FOR %PER% = %TIME_LIST%
*LOOKUP RATE
*DIM TIME = TMVL(-12,%PER%)
*DIM R_ENTITY = "GLOBAL"
*DIM VERSION=%VERSION_LIST%
*DIM R_ACCT = SUMMARY_ACCOUNT.RATETYPE
*DIM LKPLC:INPUTCURRENCY = E_ENTITY.CURRENCY
*DIM LKPSUB:INPUTCURRENCY = E_ENTITY.SUBMISSION_CURRENCY
*ENDLOOKUP

*XDIM_MEMBERSET E_ENTITY = BAS(%E_ENTITY_SET%)
*XDIM_MEMBERSET TIME = BAS(%PER%)

*WHEN RPTCURRENCY
*IS Subm
*WHEN E_ENTITY.SUBMISSION_CURRENCY
*IS <> ""
*WHEN SUMMARY_ACCOUNT.RATETYPE
*IS <> ""
//Convert From SUBMISSION

*REC(EXPRESSION = %VALUE%*LOOKUP(LKPSUB),RPTCURRENCY = USD)

*ELSE
*REC(FACTOR=1,RPTCURRENCY = USD)

*ENDWHEN
*ENDWHEN
*ENDWHEN

*NEXT

[former_member186338]

Sorry, but your code is absolutely incorrect!

LOOKUP can't be used insed FOR/NEXT. Please read help!

[former_member186338]

P.S. And have to repeat - FOR/NEXT is a wrong way, resulting in complex and slow code!

What is the issue with property creation???

[Former Member]

Hi Vadim Kalinin,

So LOOKUP shouldn't use in FOR/NEXT loop means my requirement will not fulfill through LOOKUP with FOR/NEXT loop.

The other alternative which you advised one is new property creation PREVYEARMONTH and use it.

If I replace *DIM TIME = TMVL(-12,%PER%) statement with *DIM TIME = ME.PREVYEARMONTH still I need to use this statement in LOOKUP. Here I also will get same issue that LOOKUP will not support multiple values If user selects multiple months.

Please advise.

*SELECT(%VERSION_LIST%,FX_SOURCE_CATEGORY,VERSION,ID=%VERSION_SET%)

*LOOKUP RATE
*DIM TIME = TIME.PREVYEARMONTH
*DIM R_ENTITY = "GLOBAL"
*DIM VERSION=%VERSION_LIST%
*DIM R_ACCT = SUMMARY_ACCOUNT.RATETYPE
*DIM LKPLC:INPUTCURRENCY = E_ENTITY.CURRENCY
*DIM LKPSUB:INPUTCURRENCY = E_ENTITY.SUBMISSION_CURRENCY
*ENDLOOKUP

*XDIM_MEMBERSET E_ENTITY = BAS(%E_ENTITY_SET%)
*XDIM_MEMBERSET TIME = TIME.PREVYEARMONTH
*WHEN RPTCURRENCY
*IS Subm
*WHEN E_ENTITY.SUBMISSION_CURRENCY
*IS <> ""
*WHEN SUMMARY_ACCOUNT.RATETYPE
*IS <> ""
//Convert From SUBMISSION

*REC(EXPRESSION = %VALUE%*LOOKUP(LKPSUB),RPTCURRENCY = USD)

*ELSE
*REC(FACTOR=1,RPTCURRENCY = USD)

*ENDWHEN
*ENDWHEN
*ENDWHEN

[former_member186338]

"So LOOKUP shouldn't use in FOR/NEXT loop" - incorrect, FOR/NEXT can be used inside LOOKUP, read help!

Your script is correct in general but not this line:

*XDIM_MEMBERSET TIME = TIME.PREVYEARMONTH

incorrect line, read help!

The rest is OK!

[former_member186338]

P.S. Try to understand that LOOKUP is using current TIME member from WHEN/ENDWHEN loop.

*DIM TIME = TIME.PREVYEARMONTH

This line will get previous year member based on current member of WHEN/ENDWHEN

[Former Member]

Hi Vadim Kalinin,

My intention is LOOKUP shouldn't use inside FOR/NEXT loop. And FOR/NEXT can be used inside LOOKUP , but it is not help to resolve my problem.

You mentioned the below line in-correct.

*XDIM_MEMBERSET TIME = TIME.PREVYEARMONTH

I changed to as below (TIME is user selection from prompt for 12 months)

*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)

Just I have updated complete code, please validate.

I am not clear on your statment "Try to understand that LOOKUP is using current TIME member from WHEN/ENDWHEN loop."

Previous I understood - LOOKUP block will execute first and results will pass to WHEN/ENDWHEN loop. After your statement, WHEN/ENDWHEN loop will execute first and inside WHEN loop the LOOKUP keyword will call the LOOKUP/ENDLOOKUP block .

Please correct me, if i misuderstood.

*SELECT(%VERSION_LIST%,FX_SOURCE_CATEGORY,VERSION,ID=%VERSION_SET%)

*LOOKUP RATE
*DIM TIME = TIME.PREVYEARMONTH
*DIM R_ENTITY = "GLOBAL"
*DIM VERSION=%VERSION_LIST%
*DIM R_ACCT = SUMMARY_ACCOUNT.RATETYPE
*DIM LKPLC:INPUTCURRENCY = E_ENTITY.CURRENCY
*DIM LKPSUB:INPUTCURRENCY = E_ENTITY.SUBMISSION_CURRENCY
*ENDLOOKUP

*XDIM_MEMBERSET E_ENTITY = BAS(%E_ENTITY_SET%)
*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)

*WHEN RPTCURRENCY
*IS Subm
*WHEN E_ENTITY.SUBMISSION_CURRENCY
*IS <> ""
*WHEN SUMMARY_ACCOUNT.RATETYPE
*IS <> ""
*REC(EXPRESSION = %VALUE%*LOOKUP(LKPSUB),RPTCURRENCY = USD)
*ELSE
*REC(FACTOR=1,RPTCURRENCY = USD)
*ENDWHEN
*ENDWHEN
*ENDWHEN

[former_member186338 · Accepted solution]

Your issue is related to TMVL function and LOOKUP in general...

*DIM TIME = TMVL(-12,%TIME_SET%)

TMVL from multiple months containing in %TIME_SET% will result in TMVL(-12, <lowest month in %TIME_SET%>)

For example: %TIME_SET%=01.2018, 02.2018, 03.2018

Then: TMVL(-12,%TIME_SET%) = 01.2017

From help: https://help.sap.com/viewer/a2049170bfeb4178ace32222842c3ec1/10.1/en-US/c8d961d0dbfd4745af4b66727957...

"variables, like %TIME_SET%

• The first period of the TIME_SET is used as the base period for a negative offset and the last period of the TIME_SET is used as the base period for a positive offset."

In order to work with multiple months in %TIME_SET% you have to create a property in TIME dimension like PREVYEARMONTH and fill it:

01.2018 - 01.2017

02.2018 - 02.2017

...

Then you can use:

*DIM TIME = TIME.PREVYEARMONTH 

Time member for LOOKUP will be selected based on the property.

[former_member186338 · Accepted solution]

Another way to implement the same logic not using new property will be using of FOR/NEXT loop for TIME in LOOKUP and in WHEN/ENWHEN. But it's slow and not recommended!

[former_member186338]

This code:

*SELECT(%VERSION_LIST%,FX_SOURCE_CATEGORY,VERSION,ID=%VERSION_SET%)
*LOOKUP RATE
*DIM TIME = TIME.PREVYEARMONTH
*DIM R_ENTITY = "GLOBAL"
*DIM VERSION=%VERSION_LIST%
*DIM R_ACCT = SUMMARY_ACCOUNT.RATETYPE
*DIM LKPLC:INPUTCURRENCY = E_ENTITY.CURRENCY
*DIM LKPSUB:INPUTCURRENCY = E_ENTITY.SUBMISSION_CURRENCY
*ENDLOOKUP

*XDIM_MEMBERSET E_ENTITY = BAS(%E_ENTITY_SET%)
*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)
*WHEN RPTCURRENCY
*IS Subm
*WHEN E_ENTITY.SUBMISSION_CURRENCY
*IS <> ""
*WHEN SUMMARY_ACCOUNT.RATETYPE
*IS <> ""
*REC(EXPRESSION = %VALUE%*LOOKUP(LKPSUB),RPTCURRENCY = USD)
*ELSE
*REC(FACTOR=1,RPTCURRENCY = USD)
*ENDWHEN
*ENDWHEN
*ENDWHEN

Is OK!

LOOKUP/ENDLOOKUP - is a definition, not a directly executed code.

LOOKUP will be executed at each iteration of WHEN/ENDWHEN loop with current members of WHEN/ENDWHEN loop passed to LOOKUP.

In this case we can look on E_ENTITY and TIME

If current member of TIME in WHEN/ENDWHEN is 2019.02 then LOOKUP will get data from 2018.02 stored in PREVYEARMONTH property of 2019.02

Hope it's clear!

Try to execute script in UJKT

P.S. Same approach with E_ENTITY

[Former Member]

Hi Vadim Kalinin,

Thanks for the inputs. I will test above code.

But I realised that One of property RATE_YEAR is available for dimension "Version" (
Version is Catogory dimension )and kept the value as "-1".
Based on this property, the exchange rates should read from previous year.

But the property RATE_YEAR is not picking the previous year exchange rates. shall it require to explictly mention RATE_YEAR property in my code to work or will it read the property without coding.

My above query was related to my original query but not related with alternative solution (New property creation)

The Original code again:

*LOOKUP RATE
//*DIM TIME = TMVL(-12,%TIME_SET%)
*DIM TIME = TIME.ID
*DIM R_ENTITY = "GLOBAL"
*DIM VERSION=%VERSION_LIST%
*DIM R_ACCT = SUMMARY_ACCOUNT.RATETYPE
*DIM LKPLC:INPUTCURRENCY = E_ENTITY.CURRENCY
*DIM LKPSUB:INPUTCURRENCY = E_ENTITY.SUBMISSION_CURRENCY
// Shall I require to add here RATE_YEAR property for VERSION to get 
// Previous year exchange rate.
*ENDLOOKUP
*XDIM_MEMBERSET E_ENTITY = BAS(%E_ENTITY_SET%)
*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)
*WHEN RPTCURRENCY
*IS Subm
*WHEN E_ENTITY.SUBMISSION_CURRENCY
*IS <> ""
*WHEN SUMMARY_ACCOUNT.RATETYPE
*IS <> ""
*REC(EXPRESSION = %VALUE%*LOOKUP(LKPSUB),RPTCURRENCY = USD)
*ELSE
*REC(FACTOR=1,RPTCURRENCY = USD)
*ENDWHEN
*ENDWHEN
*ENDWHEN

[former_member186338]

kittu25

You need to get some very basic training in BPC!

The property in CATEGORY dimension is related to the standard FX procedure using *RUN_PROGRAM CURR_CONVERSION

not you custom WHEN/ENDWHEN!!!!

[former_member186338]

kittu25

P.S. And the answer to your next question - can we use standard *RUN_PROGRAM CURR_CONVERSION?

No, you can't!

Standard program convert ONLY LC (Local Currency) and you want to convert "Subm"