Opening and Closing Values in Script Logic

[Former Member]

Hello,

I need to calculate in script logic the values of the closing value of the month based on the opening value and the purchases and sales of the month, for all the months of the 6 years in the budget process. The opening value of the month is the closing value of the previous month.

Example:

                        2015.Jan    2015.Feb      2015.Mar

OpeningValue     1000          950               1100

Purchases             50          200                50

Sales                    100        50                 250

ClosingValue         950        1100               900

I've achieved this with the following script:

*SELECT(%FIRST_YEAR%, YEAR, CATEGORY,ORC_ACTIVE=1)

*SELECT(%LAST_YEAR%, LAST_YEAR, CATEGORY,ORC_ACTIVE=1)

*SELECT(%T_BUDGET%, ID, PERIOD, "YEAR>='%FIRST_YEAR%' And YEAR<=%LAST_YEAR% And LEVEL='MONTH'")

*XDIM_MEMBERSET PERIOD = %T_BUDGET%

*XDIM_MEMBERSET RUBRICA_INV_DIV = OpeningValue,Purchases,Sales

*WHEN_REF_DATA=TRANS_DATA

*FOR %PER% = %T_BUDGET%

*WHEN PERIOD

*IS %PER%

  *REC(RUBRICA_INV_DIV=ClosingValue)

  *REC(RUBRICA_INV_DIV=OpeningValue,PERIOD=PERIOD.NEXT)

*ENDWHEN

*NEXT

The NW version doesn't allow the use of CALC_EACH_PERIOD, so the only solution I found so far was the use of *FOR/*NEXT, but because I have to run this for 72 months the performance is really bad.

Is there any way to increase the execution speed of this script?

Thanks!

[former_member186338 · Accepted solution]

Just to confirm:

What do you have in NEXT property for 2015.DEC?

Vadim

[Former Member · Accepted solution]

2016.JAN...

Lara

[former_member186338 · Accepted solution]

And some more info:

Model storage type PER/YTD?

ACCOUNT dimension? ACCTYPE?

RUBRICA_INV_DIV - "S" type dimension?

Vadim

[Former Member · Accepted solution]

The model storage type is Periodic.

The account dimension is the RUBRICA_INV_DIV dimension, the account types are:

Opening Value - LEQ

Closing Value - LEQ

Purchases - INC

Sales - EXP

Thanks!

[former_member186338 · Accepted solution]

I have analyzed your requirements and unfortunately found that there is no easy way to remove slow FOR/NEXT in this scenario...

You can try to use YTD measure for (Purchases+Sales) + Opening for Jan within one year to fill Closing:

Something like (not tested!):

//Let's add temporary Movements account (INC)

*XDIM_MEMBERSET PERIOD=BAS(2015.TOTAL) //for 2015!

*XDIM_MEMBERSET RUBRICA_INV_DIV = Purchases,Sales,OpeningValue

*WHEN RUBRICA_INV_DIV

*IS Purchases,Sales

*REC(EXPRESSION=%VALUE%,RUBRICA_INV_DIV=Movements)

*IS OpeningValue

*WHEN PERIOD.MONTHNUM

*IS 1 //JAN!

*REC(EXPRESSION=%VALUE%,RUBRICA_INV_DIV=Movements) //only for JAN

*ENDWHEN

*ENDWHEN

*XDIM_MEMBERSET RUBRICA_INV_DIV = Movements

*XDIM_MEMBERSET MEASURES=PERIODIC //required!

//slow loop - due to YTD calculations!

*WHEN RUBRICA_INV_DIV

*IS *

*REC(EXPRESSION=[MEASURES].[YTD],RUBRICA_INV_DIV=ClosingValue)

*ENDWHEN

*XDIM_MEMBERSET RUBRICA_INV_DIV=ClosingValue

*WHEN RUBRICA_INV_DIV

*IS *

*REC(EXPRESSION=%VALUE%,PERIOD=PERIOD.NEXT,RUBRICA_INV_DIV=OpeningValue)

*ENDWHEN

//next year!

Vadim

[Former Member · Accepted solution]

I tried to use your approach and the execution speed was reduced to less than half the time.

Thank you for your help!