Skip to Content
0

BPC Script - Lookup current and previous month rate

Mar 21 at 12:29 PM

26

avatar image

Hi expert,

Is it possible to get the current and previous month exchange rate using lookup? I am trying to get the current month exchange rate amount and deduct with previous month exchange rate translation amount. I have done some research and know that we could use *FOR in lookup and cannot use TMVL in lookup. If I use *FOR for TIME in lookup, how should it be called in the calculation? I need previous month rate in the code highlighted in bold below. Could you please advise on this? Appreciate your help on this.

*LOOKUP Rates
*DIM RT:R_ACCOUNT = AVG
*DIM INPUTCURRENCY = ENTITY.CURRENCY
*DIM R_ENTITY = Global
*DIM CATEGORY = CATEGORY
*DIM TIME = TIME
*ENDLOOKUP

*FOR %CTIME% = %TIME_SET%
*XDIM_MEMBERSET TIME = %CTIME%
*WHEN ENTITY.CURRENCY
*IS USD
*REC(FACTOR = 1, RPTCURRENCY = USD)
*ELSE
*REC(EXPRESSION = %VALUE%/LOOKUP(RT)) - (([TIME].[TMVL(-1,%CTIME%)])/LOOKUP(RT)), RPTCURRENCY = USD)
*ENDWHEN
*NEXT

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Vadim Kalinin Mar 21 at 02:53 PM
0

The requirement is strange and can be done in a different way. But the required business logic has to be explained.

In theory you can create a PREVMONTH property in time dimension, fill it with previous month id’s and use lookup by property. But better to explain what do you want to achieve!

Share
10 |10000 characters needed characters left characters exceeded
Dione L Apr 03 at 06:38 AM
0

Hi Vadim,

Thanks for the advice. I have tried using previous month property in time dimension and it works. The business actually wants to calculate currency translation using YTD amount in periodic model. So we have to get current month YTD currency translation amount minus previous month YTD currency translation amount to get the current month translated periodic amount to be written to the periodic model.

Share
10 |10000 characters needed characters left characters exceeded