Former Member

### script logic to calculate periodic values in a YTD model

script-v1.txtHi Experts,

We write a script to take GBP values and multiplies by the rate in the rate model and post it to JPY currency in a YTD model.

As it is a ytd model, the script is working fine for first time period 2016.P01(For Apr YTD & periodic are same), however if i run the script for period 2016.p02, it is converting May ytd(Apr+may) value into JPY currency which is not we are expecting, we want to convert may periodic value into JPY for period 2.

Please see attached for the script.

Thanks.

script-v1.txt (1.1 kB)
10|10000 characters needed characters exceeded

Mar 22, 2017 at 12:47 PM

Sorry, just forget to add correct time and currency destination:

```//%TIME_SET%-single month!andNotfirst month of the year!
*LOOKUP Rates
*DIM JPYCURR: INPUTCURRENCY = JPY
*DIM GBPCURR: INPUTCURRENCY = GBP
*DIM R_ACCOUNT = AVG
*DIM R_ENTITY = GLOBAL
*DIM TIME = %TIME_SET%
*DIM MEASURES = PERIODIC
*ENDLOOKUP
*XDIM_MEMBERSET ACCOUNT = 489030
*XDIM_MEMBERSET AUDITTRAIL = <ALL>
*XDIM_MEMBERSET CONSPARTNER = <ALL>
*XDIM_MEMBERSET FLOW = PL99
*XDIM_MEMBERSET MEASURES = YTD
*XDIM_MEMBERSET CONSUNIT = <ALL>
*XDIM_MEMBERSET RPTCURRENCY = GBP
*XDIM_MEMBERSET TIME=%TIME_SET%,TMVL(-1,%TIME_SET%) //%TIME_SET% - single month!
*WHEN TIME
*IS %TIME_SET%
*REC(EXPRESSION=%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY)
*IS TMVL(-1,%TIME_SET%)
*WHEN TIME.MONTHNUM
*IS <> 12 //not the last month of previous year!
*REC(EXPRESSION=-%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY,TIME=%TIME_SET%)
*ENDWHEN
*ENDWHEN```

Look on changed lines:

`*REC(EXPRESSION=%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY)`

and

`*REC(EXPRESSION=-%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY,TIME=%TIME_SET%)`
10|10000 characters needed characters exceeded

Yes you are correct.

• Mar 22, 2017 at 09:18 AM

Absolutely strange script!

What for:

```*WHEN FLOW
*IS *

*REC(EXPRESSION=1,RPTCURRENCY=%RPTCURRENCY_SET%,TIME=%TIME_SET%)
*ENDWHEN```

Second - to perform currency conversion on periodic value try:

`*REC(EXPRESSION=([MEASURES].[PERIODIC]*LOOKUP(JPYCURR)/LOOKUP(GBPCURR)))`
10|10000 characters needed characters exceeded

I have tried your script with small scope and run the script for period 2.

and the result it is posting to GBP currency as the scope is set to GBP.

One more question.

```//%TIME_SET% - single month! and Not first month of the year!

and what if i need to select the first month of the year?

```
script-v2.txt (703 B)
• Mar 22, 2017 at 12:07 PM

If the script is launched with %TIME_SET%= first month of the year then TMVL(-1,%TIME_SET%) will be the last month of the previous year and we do not want to subtract it! Then:

```//%TIME_SET% - single month! and Not first month of the year!
*LOOKUP Rates
*DIM JPYCURR: INPUTCURRENCY = JPY
*DIM GBPCURR: INPUTCURRENCY = GBP
*DIM R_ACCOUNT = AVG
*DIM R_ENTITY = GLOBAL
*DIM TIME = %TIME_SET%
*DIM MEASURES = PERIODIC
*ENDLOOKUP

*XDIM_MEMBERSET ACCOUNT = 489030
*XDIM_MEMBERSET AUDITTRAIL = <ALL>
*XDIM_MEMBERSET CONSPARTNER = <ALL>
*XDIM_MEMBERSET FLOW = PL99
*XDIM_MEMBERSET MEASURES = YTD
*XDIM_MEMBERSET CONSUNIT = <ALL>
*XDIM_MEMBERSET RPTCURRENCY = GBP

*XDIM_MEMBERSET TIME=%TIME_SET%,TMVL(-1,%TIME_SET%) //%TIME_SET% - single month!
*WHEN TIME
*IS %TIME_SET%
*REC(EXPRESSION=%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR))
*IS TMVL(-1,%TIME_SET%)
*WHEN TIME.MONTHNUM
*IS <> 12 //not the last month of previous year!
*REC(EXPRESSION=-%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR))
*ENDWHEN
*ENDWHEN```
10|10000 characters needed characters exceeded