0

# script logic to calculate periodic values in a YTD model

Mar 22, 2017 at 08:57 AM

187

Former Member

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)

Vadim Kalinin Mar 22, 2017 at 12:47 PM
0

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%)`
Show 9 Share

And some explanation:

First you scope:

*XDIM_MEMBERSET RPTCURRENCY = GBP //as source values in GBP currency

*XDIM_MEMBERSET TIME=%TIME_SET%,TMVL(-1,%TIME_SET%) // selected and previous month

Currency rate are for selected month (in LOOKUP selected month is used)

1. Convert GBP to JPY for selected month with the rate for selected month and store result in JPY

2. Convert GBP to JPY for previous month with the rate for selected month and store result in JPY with minus.

Aggregation result will be a periodic value converted to JPY using selected month rate.

period 1 is ok, still not getting correct number for period 2.

My rate for period 2 is 122.836

but it is converting at 123.94555

Please check the attached screen shot.capture.png

capture.png (20.4 kB)
Srikanth Godavarthi

Sorry, but this is not possible!

The rate month is fixed in the LOOKUP in my script:

*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

If %TIME_SET% is period 02 then the rate will be for period 02

Srikanth Godavarthi

Is it what you want to achieve for 2016.P02:

(([RPTCURRENCY].[GBP],[TIME].[2016.P02]) -

([RPTCURRENCY].[GBP],[TIME].[2016.P01])) * RATE(JPY,2016.P02)/RATE(GBP,2016.P02)

Is it what you want to achieve for 2016.P02:

(([RPTCURRENCY].[GBP],[TIME].[2016.P02]) -

([RPTCURRENCY].[GBP],[TIME].[2016.P01])) * RATE(JPY,2016.P02)/RATE(GBP,2016.P02)

Srikanth Godavarthi

Then my script will do exactly what you want!

For TIME=2016.P02 (%TIME_SET%)

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

Both lookups will get rate for 2016.P02

([RPTCURRENCY].[GBP],[TIME].[2016.P02]) * RATE(JPY,2016.P02)/RATE(GBP,2016.P02)

.

For TIME=2016.P01 (TMVL(-1,%TIME_SET%))

*REC(EXPRESSION=-%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY,TIME=%TIME_SET%)

Both lookups will get rate for 2016.P02

- ([RPTCURRENCY].[GBP],[TIME].[2016.P01]) * RATE(JPY,2016.P02)/RATE(GBP,2016.P02)

No, i am getting the same result.

If i select periodic measure my expected result is below.

However if i run report for YTD measure i am getting the value what i am expecting for periodic measure, please see below JPY value for period 2 and above figure expected result..

Regards,

Srikanth

capture.png (18.6 kB)
capture1.png (17.9 kB)
Srikanth Godavarthi

Sorry, but the script is storing PERIODIC values in YTD cube - it was your request!

And you will see correct calculation results if you select YTD measure!

YTD measure will show figures as is on base members.

Yes you are correct.

Vadim Kalinin Mar 22, 2017 at 09:18 AM
0

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)))`
Show 3 Share
Former Member

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

I write this for posting 1 value to the target before running the below script.

and i have modified the script as per your suggestion and it is taking very long time to execute and it impacts the whole system performance, so i have cancelled the job after 10 min.

script-v1.txt```
script-v1.txt (1.4 kB)
Former Member

"I write this for posting 1 value to the target before running the below script." - useless!

*REC(EXPRESSION=0) is also useless!

"it is taking very long time to execute"

For sure calculation of periodic from YTD takes time!

To test - try small scope to ensure that you get a correct results.

You can also test the script working for single month:

`//%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%)*REC(EXPRESSION=-%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR))*ENDWHEN`

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)
Vadim Kalinin Mar 22, 2017 at 12:07 PM
0

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```
Show 1 Share