Master Calendar Cube in TM1 (Planning Analytics)

Written By tiedot

Extending from the last post on incrementing dates in Planning Analytics (TM1), this post looks at how to build a simple Master Calendar cube that can be leveraged in rules and TurboIntegrator (TI) processes.

There are a few parts to this post but basically, you will need to build some dimensions and a cube and then add in rules. It's know I have listed the cube first so the order of the steps isn’t accurate, but hey…

Cube Structure

The master calendar cube has three dimensions:

  1. Year

  2. Month

  3. Calendar Measure

Year represents your annual period, this could be the calendar year or more likely the fiscal year eg in Australia the standard financial years runs from July through to June. We have assumed we don’t need a daily breakdown, though if you need that it can be added in as a Day dimension.

The Year dimension also needs a few attributes (see below) that are useful both for reporting and making some of the rules on the calendar cube easier to work with.

Month represents each of the months ie Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun. All you need is a unique element for each month though the short codes usually makes it easier to navigate for end users. Bonus points if you have an alias that represents the month as its number.

Calendar measure is the last dimension in the cube and has string and numeric measures as per the rules below (if the rule has an N at the start then it’s a numeric element, if it shows an S, then the element must be a string). You can add in other measures for public holidays, flagging whether the period is ‘closed’ from a financial perspective and lots of other date specific assumptions. I have kept it to the basics though it’s a long enough post already.


Year dimension attributes

  • The Year dimension needs the following as text attributes:

  • Next

  • Prev

  • Start

  • End

  • Part a

  • Part b

Month Attributes

The Month dimension needs the following attributes, these can be manually updated as the months won’t be changing any time soon, unlike year which changes at least once per year….

The Cal Period attribute holds the period number eg Jan has a value of 1. The financial year component has a value of a or b, as it maps the period to the correct year eg Jul - Dec would be an and Jan - Jun would be b. There are a multitude of ways of doing this but this works for me.

  • Next

  • Prev

  • Cal Period

  • Financial year component

Year dimension attribute rules

The following rules have to be added to the year attributes control cube eg }element attributes_year. 

# Rules to automatically calculate attributes against financial year dimension 
# Assumes structure of YYYY/YYYY 

# Prevent rules working against consolidations (Next) 
['Next'] = S: If ( Ellev ( 'Year', !Year) >0 , Stet, Continue ); 
 
# Calculate values for Next and Prev attributes (Next) 
['Next'] = S: Str ( Numbr ( SubSt ( !Year, 1, 4 ) ) + 1, 4, 0 ) | '/' | Str ( Numbr ( SubSt ( !Year, 6, 4 ) ) + 1, 4, 0 ); 
 
# Prevent rules working against consolidations (Prev) 
['Prev'] = S: If ( Ellev ( 'Year', !Year ) > 0, Stet, Continue ); 

# Calculate values for Next and Prev attributes (Prev) 
['Prev'] = S: Str ( Numbr ( SubSt ( !Year, 1, 4 ) ) - 1, 4, 0 ) | '/' | Str ( Numbr ( SubSt ( !Year, 6, 4 ) ) - 1, 4, 0 ); 
 
# Calculate start and end dates 
['Start'] = S: Str ( DayNo ( SubSt ( !Year, 1, 4 ) | '-07-01') + 21916, 5, 0 ); 
['End'] = S: Str ( DayNo ( SubSt ( !Year, 6, 4 ) | '-06-30') + 21916, 5, 0 ); 
 
# Calculate part a (left) 
['Part a'] = S: If ( Ellev ( 'Year', !Year ) > 0, Stet, Continue ); 
['Part a'] = S: SubSt ( !Year, 1, 4 ); 
 
# Calculate part a (right) 
['Part b'] = S: If ( Ellev ( 'Year', !Year ) > 0, Stet, Continue ); 
['Part b'] = S: SubSt ( !Year, 6, 4 );

Calendar cube rules

In the rules, it’s assumed the calendar cube is called Calendar, so while not imaginative, it does what it says on the tin.

# CALENDAR CUBE RULES
# DB ( 'Calendar', !Year, !Month, !Calendar Measure )
 
  
# Work out first day in month 
['Month Start'] = S: If ( ElLev ( 'Month', !Month ) = 0, Date ( DayNo ( Attrs ( 'Year', !Year, 'Part ' | Attrs ( 'Month', !Month, 'Financial Year Component' ) ) | '-' | Attrs ( 'Month', !Month, 'Cal Period' ) | '-01' )), ''); 

['Month End'] = S: If ( ElLev( 'Month', !Month ) = 0, Date ( DayNo ( Attrs ( 'Year', !Year, 'Part ' | Attrs ( 'Month', If ( !Month @= 'Jun', 'Jun', Attrs ( 'Month', !Month, 'Next' ) ), 'Financial Year Component')) | '-' | Attrs ( 'Month', Attrs ( 'Month', !Month, 'Next' ), 'Cal Period') | '-01')-1), ''); 
 

# Calculate the days in month 
['Days in Month'] = N: 
    
    # Find last day of next month and then subtract one then present as number of days 
    Day ( Date ( DayNo ( Attrs ( 'Year', !Year, 'Part ' | Attrs ( 'Month', Attrs ( 'Month', !Month, 'Next' ), 'Financial Year Component' )) | '-' | Attrs ( 'Month', Attrs ( 'Month', !Month, 'Next' ), 'Cal Period' ) | '-01' )-1 ) ); 
  


# Calculate work days in month ie Monday to Friday

['Work Days In Month'] =  N: 

      # Only calculate if flag set to 1 in calendar cube 
      If ( ['Auto Calculate Work Days In Month?', 'Jul'] = 1, 
    
        # Yes, auto calculate 
        # Work out number of weeks and multiply by 5 
        Int ( ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month End' ) ) - DayNo ( DB ( 'Calendar', !Year, !Month, 'Month Start' ) ) ) / 7 ) * 5 
    
        # Add on remaining days 
        + Mod ( ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month End' ) ) - DayNo ( DB ( 'Calendar', !Year, !Month, 'Month Start' ) ) ), 7 ) 
        
        # Add in adjustment factor for number of weekends and bits from leap years etc 
        + 
        If ( Mod ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month Start' ) ) + 21914, 7 ) <= Mod ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month End' ) ) + 21914, 7),  
            If ( Mod ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month Start' ) ) + 21914, 7 ) <= 6 & Mod ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month End' ) ) + 21914, 7 ) <= 6, -1, 
                If ( Mod ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month Start' ) ) + 21914, 7 ) <= 7 & Mod ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month End' ) ) + 21914, 7 ) <= 7, -1, 
                    0)) 
                        , 
                        If ( Mod ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month Start' ) ) + 21914, 7 ) = 7, -1, 
                            
                            If ( Mod ( DayNo ( DB ( 'Calendar', !Year, !Month, 'Month End' ) ) + 21914, 7 ) = 6, -1, 
                                
                                -2 
                            
                            ) 
                        ) 
  
        ), 
      
            # No, so Stet 
            Stet 
      
      );
tiedot
Next

Incrementing a Date