Skip to Content
0
Jul 06, 2015 at 08:31 AM

How to convert data in Periodic model with a YTD rate?

148 Views

Hi experts,

I have the following questions:

My P&L model is a Peridic model which has no Substables (Flow) dimension so I can't use Business Rules.

In my model I have 3 currencies: EUR, CHF, BRL.

I need to convert datas with a YTD rate.

For example (datas are Periodic)

Account;Entity;Currency;Period;Amount

701000;Brazil;BRL;JAN;100

701000;Brazil;BRL;FEB;50

My rate is 3,243668 for JAN

My rate is 3,25056 for FEB

These rates are Average Cumulated Rate from the 1/1/2014.

So I need to realize following calculs:

For JAN: 100/3,243668 = 30,83

For FEB: 150/3,25056 = 46,15

So I have to read results like that in Periodic measure:

For JAN:

30,83

For FEB:

15,32 (46,15 - 30,83)

To convert datas, I need to use a script which allows to convert FEB datas in YTD, calculate difference between JAN and FEB and write result on FEB.

I use following script:

*LOOKUP TAUX

*DIM FXRATENTITY="GLOBAL"

*DIM FXRATE=COMPTE.RATETYPE

*DIM EUR:INPUTCURRENCY=CENTRE_COUT.CURRENCY

*DIM MEASURES="PERIODIC"

*ENDLOOKUP

*FOR %PERIODE%=%TEMPS_SET%

*WHEN COMPTE.RATETYPE

*IS AVG,CLO,OPE

*REC(EXPRESSION=((([MEASURES].[YTD])*1/lookup(EUR))-([MEASURES].[YTD],TEMPS.TMVL(-1,%PERIODE%),[DEVISE].[EUR])),DEVISE="EUR",TEMPS.TMVL(0,%PERIODE%))

*ELSE

*REC(DEVISE="EUR")

*ENDWHEN

*COMMIT

*NEXT

But when I validate my script, I have the following error:

Unknown dimension name in keyword: "TEMPS.*: (Cf Attachment).

Have you an idea for the reason of this message?

TMVL is it a good syntax for BPC 10.1 NW?

Have you another solution to realize my calculation?

Thanks for your help.

Alexandre