Skip to Content
Technical Articles
Author's profile photo Vadim Kalinin

How to calculate next or previous time member in BPC script logic

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, format can be YYYYMM00):

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:

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

// Previous Year
//%TIME_SET%=2007.04
*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //%Y%=2007
*SELECT(%TIDM01%,[TIMEID],TIME,YEAR=%Y% AND MONTHNUM=1 AND CALC=N) //%TIDM01%=20070100
*SELECT(%TIDL%,[TIMEID],TIME,TIMEID<%TIDM01%) //%TIDL%=...20061100,20061200
*SELECT(%PREVYEAR%,[YEAR],TIME,TIMEID>=%TIDL% AND TIMEID<%TIDM01%)
*XDIM_MEMBERSET TIME=%PREVYEAR%.01
//Result: *XDIM_MEMBERSET TIME=2006.01 variable %PREVYEAR%=2006

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

Questions? Comments?

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.