Skip to Content
0

script logic to calculate periodic values in a YTD model

Mar 22, 2017 at 08:57 AM

187

avatar image
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.

Please help on this.

Thanks.

script-v1.txt (1.1 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
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
10 |10000 characters needed characters left characters exceeded

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.

0

Hi Vadim,

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

Thanks for your help

capture.png (20.4 kB)
0

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

0

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)

0

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)

Yes Vadim.

0

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)

0

Hi vadim,

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)
0

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.

0

Hi Vadim,

Yes you are correct.

Many thanks for your help.

0
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
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Vadim,

Thanks for the reply.

*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.

Could you please help.

script-v1.txt
script-v1.txt (1.4 kB)
0

"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
0

script-v2.txtHi Vadim,

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?

Thanks for your help.

script-v2.txt (703 B)
0
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
10 |10000 characters needed characters left characters exceeded

Hi Vadim

I have tried the above script and it is working for the first month, however if i run the script for 2016.p02 it's not working as expected for period2 and it is also chnaging the JPY numbers for period1.

Please check the attached screen shots and the script.

after-period-1-run.pngafter-period-2-run.pngscript-v2.txt

and i have tried the attached (please check attached working script) script and it is working as expected.

Could you please have a look and let me know if this works ok.

Thanks for your help.

working-script.txt

0