Skip to Content

Traditional approach require the TMVL function. But the result of TMVL can’t be used in *SELECT statements. For example the following code will generate no result:

//%TIME_SET%=2007.04 
*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //%Y%=2007
*SELECT(%IDDEC%,[ID],TIME,YEAR=%Y% AND MONTHNUM=12 AND CALC=N) //%IDDEC%=2007.12
*SELECT(%NEXTYEAR%,[YEAR],TIME,ID=TMVL(1,%IDDEC%)) //Incorrect syntax
*XDIM_MEMBERSET TIME=%NEXTYEAR%.01
//Result: *XDIM_MEMBERSET TIME=%NEXTYEAR%.01

It’s possible to use SELECT statements to find the next member and store result in some variable.

First requirement – TIMEID property has to be properly maintained in TIME dimension (only for base members, numeric character strings in the increasing order):

The following script will calculate the next TIME member related to the member selected in %TIME_SET%:

//%TIME_SET%=2007.04 
*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%) //%TID%=20070400
*SELECT(%TIDG%,[TIMEID],TIME,TIMEID>%TID%) //%TIDG%=20070500,20070600,...
*SELECT(%NEXTPER%,[ID],TIME,TIMEID>%TID% AND TIMEID<=%TIDG%)
*XDIM_MEMBERSET TIME=%NEXTPER%
//Result: *XDIM_MEMBERSET TIME=2007.05

The same way we calculate previous TIME member:

//%TIME_SET%=2007.04 
*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%) //%TID%=20070400
*SELECT(%TIDL%,[TIMEID],TIME,TIMEID<%TID%) //%TIDG%=20070300,20070200,...
*SELECT(%PREVPER%,[ID],TIME,TIMEID>=%TIDL% AND TIMEID<%TID%)
*XDIM_MEMBERSET TIME=%PREVPER%
//Result: *XDIM_MEMBERSET TIME=2007.03

Same approach can be used to calculate next-next… or prev-prev… member.

Also next (or previous) year value can be calculated:

//%TIME_SET%=2007.04
*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //%Y%=2007
*SELECT(%TIDDEC%,[TIMEID],TIME,YEAR=%Y% AND MONTHNUM=12 AND CALC=N) //%TIDDEC%=20071200
*SELECT(%TIDG%,[TIMEID],TIME,TIMEID>%TIDDEC%) //%TIDG%=20080100,20080200,...
*SELECT(%NEXTYEAR%,[YEAR],TIME,TIMEID>%TIDDEC% AND TIMEID<=%TIDG%)
*XDIM_MEMBERSET TIME=%NEXTYEAR%.01
//Result: *XDIM_MEMBERSET TIME=2008.01 variable %NEXTYEAR%=2008

Same for next-next… or prev-prev… year.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply