Skip to Content
avatar image
Former Member

BPC LOOKUP is not calculating correctly. the value is halfed.

Jan 04, 2017 at 04:17 PM | 124 Views

Hi,

I a trying to do a currency conversion using lookup, the value is halved and translation is applied. below is the simple code.

*LOOKUP Rates
*DIM END:R_ACCOUNT = END
*DIM CATEGORY = Actual
*DIM R_ENTITY = Global
*DIM INPUTCURRENCY = ENTITY.CURRENCY
*ENDLOOKUP

*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET CATEGORY = Actual
*WHEN RPTCURRENCY
*IS LC
*REC(EXPRESSION = %VALUE%/LOOKUP(END),RPTCURRENCY = USD)
*ENDWHEN
*COMMIT

2015.11LC - LCUSD - USD105.3084192

yi8kd.png (1.9 kB)
SAP Business Planning and Consolidation, version for SAP NetWeaver
script logic lookup
Add comment
10|10000 characters needed characters exceeded

  • Former Member
    Jan 04, 2017 at 04:57 PM

    we want to pick the corresponding currency of the entity and convert to USD.

    in this example the entity AGB01 is EUR company. it has to pick the corresponding END rate from Rates model and apply %VALUE%/LOOKUP(END).

    eyuzn.png (2.3 kB)
  • Get RSS Feed

8 Answers

  • avatar image
    Vadim Kalinin
    Jan 04, 2017 at 04:47 PM

    Please provide complete info inline with https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

    From your code:

    *DIM INPUTCURRENCY = ENTITY.CURRENCY

    will get rate for the currency defined in ENTITY property CURRENCY

    Why do you think it will be USD or ...?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Vadim Kalinin
    Jan 04, 2017 at 05:05 PM

    The script to convert to any currency will be like:

    *LOOKUP Rates
    *DIM R_ACCOUNT = END
    *DIM CATEGORY = Actual
    *DIM R_ENTITY = Global
    *DIM LC_RATE:INPUTCURRENCY = ENTITY.CURRENCY
    *FOR %CUR%=EUR,USD //list of target currencies
    *DIM %CUR%_RATE:INPUTCURRENCY=%CUR%
    *NEXT
    *ENDLOOKUP

    *XDIM_MEMBERSET RPTCURRENCY = LC
    *XDIM_MEMBERSET CATEGORY = Actual
    *WHEN RPTCURRENCY
    *IS *
    *REC(EXPRESSION = %VALUE%*LOOKUP(USD_RATE)/LOOKUP(LC_RATE),RPTCURRENCY = USD)
    *ENDWHEN

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 04, 2017 at 04:43 PM

    above is the result and I am using the code in default logic

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 04, 2017 at 05:26 PM

    when I used the recommended code the value is doubled.

    *LOOKUP Rates
    *DIM R_ACCOUNT = END
    *DIM CATEGORY = Actual
    *DIM R_ENTITY = Global
    *DIM LC_RATE:INPUTCURRENCY = ENTITY.CURRENCY
    *FOR %CUR%=USD //list of target currencies
    *DIM %CUR%_RATE:INPUTCURRENCY=%CUR%
    *NEXT
    *ENDLOOKUP

    *XDIM_MEMBERSET RPTCURRENCY = LC
    *XDIM_MEMBERSET CATEGORY = Actual
    *WHEN RPTCURRENCY
    *IS *
    *REC(EXPRESSION = %VALUE%*LOOKUP(USD_RATE)/LOOKUP(LC_RATE),RPTCURRENCY = USD)
    //*REC(EXPRESSION = %VALUE%/LOOKUP(LC_RATE),RPTCURRENCY = USD)
    *ENDWHEN

    the expected value is 10.61 USD.

    When I used :

    *REC(EXPRESSION = %VALUE%/LOOKUP(LC_RATE),RPTCURRENCY = USD) the value is halved

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Vadim Kalinin
    Jan 04, 2017 at 08:41 PM

    Please test exactly this script (please check that you have nothing else in default.lgf):

    *LOOKUP Rates
    *DIM R_ACCOUNT = END
    *DIM R_ENTITY = Global
    *DIM LC_RATE:INPUTCURRENCY = ENTITY.CURRENCY
    *DIM USD_RATE:INPUTCURRENCY=USD
    *ENDLOOKUP

    *WHEN CATEGORY
    *IS Actual
    *WHEN RPTCURRENCY
    *IS LC
    *REC(EXPRESSION = %VALUE%*LOOKUP(USD_RATE)/LOOKUP(LC_RATE),RPTCURRENCY = USD)
    *ENDWHEN
    *ENDWHEN

    Also please list dimensions you have in the rate model!

    Corrected - *DIM R_ENTITY = Global

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 04, 2017 at 08:12 PM

    Below are the values from Rates model

    And in my finance model, I am inputting a value of 10 in LC (for the entity AGB01 the rptcurrency is EUR) as the currency conversion is written in default I am expecting 10 EUR = 10.616 USD which is derived as (10*1/.9419). Kindly let me know if any more information is need to get clarity.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 04, 2017 at 08:33 PM

    Below is the screen shot of the currency property of AGB01

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 04, 2017 at 08:34 PM

    Below is the screen shot of the currency property of AGB01

    Add comment
    10|10000 characters needed characters exceeded