Relative time dimensions

  • Apr. 06, 2014

Introduction

Time dimensions in TM1 have been discussed a good number of times in the past, so I will not want to do this again here. Rather, while browsing the TM1 Master Course (P6519) by IBM, I stumbled upon a screenshot of a time dimension containing relative time periods:

    The advantages of such a time dimension architecture can be substantial and can include:
  • Reports and views reference the same (consolidated) elements, only their children will change
  • Avoid nesting of 2 separate dimensions for Years and Periods
  • Easy addition of 1 alias on the dimension, attributes like Previous Period and Next Period are easier
  • Easy updating each period, once the TI process is there

    I decided to create the 2 fairly generic TI processes:
  1. 1 process to create the dimension from scratch
  2. 1 process to update the dimension by setting the current period / year

So here goes, basically, my code to produce and maintain this dimension:

1. Creating the dimension from scratch

The ingredients are a TI process with parameters pDim (Dimension Name ?), pFirstYear and pLastYear (as numeric values, for instance, 2012 and 2015). In the Advanced > Prolog tab, you paste the following code: 

# Wim Gielis
# https://www.wimgielis.com


#####
# Creating a relative time dimension
# 04/06/14
#####

If( DimensionExists( pDim ) = 0 );
  DimensionCreate( pDim );
EndIf;

#######################
# All FYs and children
#######################
DimensionElementInsert( pDim, '', 'ALL FYs', 'C' );

y = pFirstYear;
While( y <= pLastYear );
  vYear = Str( y, 4, 0 );
  DimensionElementInsert( pDim, '', vYear, 'C' );
  DimensionElementComponentAdd( pDim, 'ALL FYs', vYear, 1 );

  m = 1;
  While( m <= 13 );
    vPeriod = NumberToStringEx( m, '00', '', '' );
    vPeriodYear = 'P' | vPeriod | '-' | vYear;
    If( m <= 3);
      vQuarter = 'Q1';
    ElseIf( m <= 6);
      vQuarter = 'Q2';
    ElseIf( m <= 9);
      vQuarter = 'Q3';
    ElseIf( m <= 13);
      vQuarter = 'Q4';
    EndIf;
    vQuarterYear = vQuarter | '-' | vYear;

    DimensionElementInsert( pDim, '', vQuarterYear, 'N' );
    DimensionElementComponentAdd( pDim, vYear, vQuarterYear, 1 );

    DimensionElementInsert( pDim, '', vPeriodYear, 'N' );
    DimensionElementComponentAdd( pDim, vQuarterYear, vPeriodYear, 1 );

    m = m + 1;
  End;

  y = y + 1;
End;


#######################
# Other derived totals
#######################
DimensionElementInsert( pDim, '', 'Current Reporting Period', 'C' );
DimensionElementInsert( pDim, '', 'Current Reporting Quarter', 'C' );
DimensionElementInsert( pDim, '', 'Current Reporting Year', 'C' );
DimensionElementInsert( pDim, '', 'Last Completed Period', 'C' );
DimensionElementInsert( pDim, '', 'Last Completed Quarter', 'C' );
DimensionElementInsert( pDim, '', 'Last Completed Year', 'C' );
DimensionElementInsert( pDim, '', 'Rolling 13 Periods', 'C' );

2. Updating the relative time dimension

Each period (when the (admin) user decides so, the current period will change. We wanted to get this automated, therefore (with parameters pDim as before, and pCurrentPeriodYear like P04-2014 for example: 

# Wim Gielis
# https://www.wimgielis.com


#####
# Updating a relative time dimension
# 04/06/14
#####

# Audit checks
If( DimensionExists( pDim ) = 0 );
  ItemReject( 'Invalid Dimension Name.' );
  ProcessError;
EndIf;

If( Dimix( pDim, pCurrentPeriodYear ) = 0 );
  ItemReject( 'Invalid Current Period.' );
  ProcessError;
EndIf;


vCurrentPeriod = Subst( pCurrentPeriodYear, 1, 3 );
vCurrentYear = Subst( pCurrentPeriodYear, 5, 4 );
If( Scan( vCurrentPeriod, 'P01P02P03' ) > 0 );
  vCurrentQuarter = 'Q1';
ElseIf( Scan( vCurrentPeriod, 'P04P05P06' ) > 0 );
  vCurrentQuarter = 'Q2';
ElseIf( Scan( vCurrentPeriod, 'P07P08P09' ) > 0 );
  vCurrentQuarter = 'Q3';
ElseIf( Scan( vCurrentPeriod, 'P10P11P12P13' ) > 0 );
  vCurrentQuarter = 'Q4';
Else;
  ItemReject( 'Invalid Period to Quarter definition.' );
  ProcessError;
EndIf;
vCurrentQuarterYear = vCurrentQuarter | '-' | vCurrentYear;
vLastYear = Str( Numbr( vCurrentYear ) - 1, 4, 0 );

If( Dimix( pDim, vCurrentYear ) = 0 );
  ProcessError;
EndIf;

If( Dimix( pDim, vCurrentQuarterYear ) = 0 );
  ProcessError;
EndIf;

#######################
# All FY's and children
#######################
### See the Create part of this TI process


#######################
# Other derived totals
#######################


#######################
c0 = 'Current Reporting Period';
#######################
DimensionElementInsert( pDim, '', c0, 'C' );
If( Dtype( pDim, c0 ) @= 'C' );
  While( ElcompN( pDim, c0) > 0 );DimensionElementComponentDelete( pDim, c0, Elcomp( pDim, c0, 1 ));End;
  DimensionElementComponentAdd( pDim, c0, pCurrentPeriodYear, 1 );
EndIf;

#######################
c0 = 'Current Reporting Quarter';
#######################
DimensionElementInsert( pDim, '', c0, 'C' );
If( Dtype( pDim, c0 ) @= 'C' );
  While( ElcompN( pDim, c0) > 0 );DimensionElementComponentDelete( pDim, c0, Elcomp( pDim, c0, 1 ));End;
  DimensionElementComponentAdd( pDim, c0, vCurrentQuarterYear, 1 );
EndIf;

#######################
c0 = 'Current Reporting Year';
#######################
DimensionElementInsert( pDim, '', c0, 'C' );
If( Dtype( pDim, c0 ) @= 'C' );
  While( ElcompN( pDim, c0) > 0 );DimensionElementComponentDelete( pDim, c0, Elcomp( pDim, c0, 1 ));End;
  DimensionElementComponentAdd( pDim, c0, vCurrentYear, 1 );
EndIf;

#######################
c0 = 'Last Completed Period';
#######################
DimensionElementInsert( pDim, '', c0, 'C' );
If( Dtype( pDim, c0 ) @= 'C' );
  While( ElcompN( pDim, c0) > 0 );DimensionElementComponentDelete( pDim, c0, Elcomp( pDim, c0, 1 ));End;

  If( vCurrentPeriod @= 'P01' );
    vLastPeriod = 'P13';
    vLastPeriodYear = vLastPeriod | '-' | Str( Numbr( vCurrentYear ) - 1, 4, 0 );
  Else;
    vLastPeriod = 'P' | NumberToStringEx( Numbr( Subst( vCurrentPeriod, 2, 2 ) ) - 1, '00', '', '' );
    vLastPeriodYear = vLastPeriod | '-' | vCurrentYear;
  EndIf;
  DimensionElementComponentAdd( pDim, c0, vLastPeriodYear, 1 );

EndIf;

#######################
c0 = 'Last Completed Quarter';
#######################
DimensionElementInsert( pDim, '', c0, 'C' );
If( Dtype( pDim, c0 ) @= 'C' );
  While( ElcompN( pDim, c0) > 0 );DimensionElementComponentDelete( pDim, c0, Elcomp( pDim, c0, 1 ));End;

  If( vCurrentQuarter @= 'Q1' );
    vLastQuarter = 'Q4';
    vLastQuarterYear = vLastQuarter | '-' | Str( Numbr( vCurrentYear ) - 1, 4, 0 );
  Else;
    vLastQuarter = 'Q' | Str( Numbr( Subst( vCurrentQuarter, 2, 1 ) ) - 1, 1, 0 );
    vLastQuarterYear = vLastQuarter | '-' | vCurrentYear;
  EndIf;
  DimensionElementComponentAdd( pDim, c0, vLastQuarterYear, 1 );

EndIf;

#######################
c0 = 'Last Completed Year';
#######################
DimensionElementInsert( pDim, '', c0, 'C' );
If( Dtype( pDim, c0 ) @= 'C' );
  While( ElcompN( pDim, c0) > 0 );DimensionElementComponentDelete( pDim, c0, Elcomp( pDim, c0, 1 ));End;

  If( vCurrentYear @<> Elcomp( pDim, 'ALL FYs', 1 ) );
    DimensionElementComponentAdd( pDim, c0, vLastYear, 1 );
  EndIf;

EndIf;

#######################
c0 = 'Rolling 13 Periods';
#######################
DimensionElementInsert( pDim, '', c0, 'C' );
If( Dtype( pDim, c0 ) @= 'C' );
  While( ElcompN( pDim, c0) > 0 );DimensionElementComponentDelete( pDim, c0, Elcomp( pDim, c0, 1 ));End;

  If( vCurrentPeriod @= 'P13' );
    vStartingPeriod = 'P01';
    vStartingYear = vCurrentYear;
  Else;
    vStartingPeriod = 'P' | NumberToStringEx( Numbr( Subst( vCurrentPeriod, 2, 2 ) ) + 1, '00', '', '' );
    vStartingYear = Str( Numbr( vCurrentYear ) - 1, 4, 0 );
  EndIf;
  vPer = vStartingPeriod;
  vYr = vStartingYear;
  vStart = vStartingPeriod | '-' | vStartingYear;
  vPerYr = vStart;

  vContinue = 1;
  While( vContinue = 1 );

    DimensionElementComponentAdd( pDim, c0, vPerYr, 1 );

    If( vPerYr @= pCurrentPeriodYear );
      vContinue = 0;
    EndIf;

    If( Subst( vPer, 1, 3) @= 'P13' );
      vPer = 'P01';
      vYr = vCurrentYear;
    Else;
      vPer = 'P' | NumberToStringEx( Numbr( Subst( vPer, 2, 2 ) ) + 1, '00', '', '' );
    EndIf;
    vPerYr = vPer | '-' | vYr;

  End;

EndIf;

The end

Voilà, enjoy the code, experiment with this code on a rainy Sunday afternoon, and see you later. For example:

  • add aliases
  • allow for a fiscal year that is not aligned with the calendar year
  • add attributes like "Previous Period" and "Next Period"
  • add additional consolidations
  • …

If you want, you can always send me your updated process code to me by email. You will get an honorable place on this much appreciated TM1 blog (or at least, that’s what I tell myself)




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links