cancel
Showing results for 
Search instead for 
Did you mean: 

script logic to calculate periodic values in a YTD model

0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

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%)
former_member186338
Active Contributor
0 Kudos

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 Kudos

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

former_member186338
Active Contributor
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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 Kudos

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.

former_member186338
Active Contributor
0 Kudos

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 Kudos

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

former_member186338
Active Contributor
0 Kudos

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 Kudos

Hi Vadim,

Yes you are correct.

Many thanks for your help.

Answers (2)

Answers (2)

former_member186338
Active Contributor
0 Kudos

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

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

former_member186338
Active Contributor
0 Kudos

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

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
former_member186338
Active Contributor
0 Kudos

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

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.