Lookup Previous year rates

[nicky_hays]

Hi,

The users enter the the planned sales revenue (A/c: SALES) data in USD. I have to convert this into LC based on previous years rate into Account SALES_HIST. I have to also convert this LC from previous step back into USD based on current years rate LC --> USD rate into account SALES_CURR. The previous year and the current year are stored in the version dimension member "BUDGET". The issue is that the rates are getting replaced. The rate lookup works correctly only if I pass one period say 2016.01 but if I pass multiple periods then the rates get replaced.

Please help!!!

I am on NW platform SAP BPC 10.1 SP 25 on SQL Server DB

*SELECT(%PYR_B%,"[PRIOR_YEAR]",VERSION,"[ID] = BUDGET")

*SELECT(%YR_B%,"[YEAR]",VERSION,"[ID] = BUDGET")

*XDIM_MEMBERSET ACCOUNT = SALES_HIST, SALES_CURR

*XDIM_MEMBERSET TIME = BAS(%YR_B%.TOTAL)

*XDIM_MEMBERSET VERSION = BUDGET

*WHEN ACCOUNT

*IS  SALES_HIST, SALES_CURR

*REC(FACTOR=0)

*ENDWHEN

*COMMIT

*LOOKUP RATES

*DIM R_ENTITY="Global"

*DIM R_ACCOUNT=ACCOUNT.RATETYPE

*DIM VERSION= "BUDGET"

*FOR %TIME_PD% = %PYR_B%.01,%PYR_B%.02,%PYR_B%.03,%PYR_B%.04,%PYR_B%.05,%PYR_B%.06,%PYR_B%.07,%PYR_B%.08,%PYR_B%.09,%PYR_B%.10,%PYR_B%.11,%PYR_B%.12,%YR_B%.01,%YR_B%.02,%YR_B%.03,%YR_B%.04,%YR_B%.05,%YR_B%.06,%YR_B%.07,%YR_B%.08,%YR_B%.09,%YR_B%.10,%YR_B%.11,%YR_B%.12

*DIM TIME=%TIME_PD%

*DIM CUSDCURR_%TIME_PD%:INPUTCURRENCY="CUSD"

*DIM LCCURR_%TIME_PD%:INPUTCURRENCY=GEOGRAPHY.CURRENCY

*NEXT

*ENDLOOKUP

*XDIM_MEMBERSET VERSION = BUDGET

*XDIM_MEMBERSET RPTCURRENCY = USD

*XDIM_MEMBERSET ACCOUNT = SALES

*XDIM_MEMBERSET TIME = BAS(%YR_B%.TOTAL)

*XDIM_MEMBERSET AUDITTRAIL = INPUT

*XDIM_MEMBERSET GEOGRAPHY = BAS(GEO_SALES)

*WHEN ACCOUNT.RATETYPE

*IS "AVG"

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

*WHEN TIME

*IS %YR_B%%PERIOD%

*REC(FACTOR=LOOKUP(CUSDCURR_%PYR_B%%PERIOD%)/LOOKUP(LCCURR_%PYR_B%%PERIOD%),RPTCURRENCY=LC,ACCOUNT=SALES_HIST)

*REC(FACTOR=(LOOKUP(CUSDCURR_%PYR_B%%PERIOD%)/LOOKUP(LCCURR_%PYR_B%%PERIOD%)) * (LOOKUP(LCCURR_%YR_B%%PERIOD%)/LOOKUP(CUSDCURR_%YR_B%%PERIOD%)),RPTCURRENCY=USD,ACCOUNT=SALES_CURR)

*ENDWHEN

*NEXT

*ENDWHEN

[former_member186338 · Accepted solution]

Hi Nicky,

The logic to implement is not clear, it's better to attach some data sample:

Then lookup is incorrect, have to be:

*FOR %TIME_PD% = %PYR_B%.01,%PYR_B%.02,%PYR_B%.03,%PYR_B%.04,%PYR_B%.05,%PYR_B%.06,%PYR_B%.07,%PYR_B%.08,%PYR_B%.09,%PYR_B%.10,%PYR_B%.11,%PYR_B%.12,%YR_B%.01,%YR_B%.02,%YR_B%.03,%YR_B%.04,%YR_B%.05,%YR_B%.06,%YR_B%.07,%YR_B%.08,%YR_B%.09,%YR_B%.10,%YR_B%.11,%YR_B%.12

*DIM CUSDCURR_%TIME_PD%:TIME=%TIME_PD%

*DIM CUSDCURR_%TIME_PD%:INPUTCURRENCY="CUSD"

*DIM LCCURR_%TIME_PD%:TIME=%TIME_PD%

*DIM LCCURR_%TIME_PD%:INPUTCURRENCY=GEOGRAPHY.CURRENCY

*NEXT

COMMIT is also useless....

Vadim

P.S. the loop has to be:

*WHEN ACCOUNT.RATETYPE

*IS "AVG"

*WHEN TIME

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

*IS %YR_B%%PERIOD%

...

[nicky_hays]

Thank you so much Vadim!!!

Nicky

[Former Member]

Why you don't want to use delivered program CURRENCY_CONVERSION. It allows using different periods (absolute and relative) for rates instead of inventing your own logic?

[nicky_hays]

Hi Gersh,

Is it possible to do a USD to LC conversion using the standard currency conversion?

I also do not know how to do currency translation for 2017 data using 2016 rates using the standard currency conversion.

Regards,

Nicky

[former_member186338]

"to LC conversion using the standard currency conversion" - not possible!

[Former Member]

Hi Nicky,

LC was introduced for users to enter data in their own currency without specifying it. So, translating from any currency to LC seems to me as something wrong with the design.

For using different period/year rates please take a look at BPC documentation for Category type Dimension Category Dimension Properties (Standard only) - SAP Business Planning and Consolidation, version for... Pay attention to Properties RATE_PERIOD and RATE_YEAR that allow absolute and relative offset.

Regards,

Gersh

[nicky_hays]

Thanks Gersh!!! I was not aware of that.

Regards,

Nicky