Leap years calculations in TM1

  • Nov. 12, 2015

Introduction

This article is quite short and simple. Today I needed to determine in TM1 if a year is a leap year or not. I came up with 3 different solutions, you might still find out others:

An attribute

The easiest solution is to add a text attribute on your dimension for years. Yes, I said it was an easy solution. Do not overcomplicate matters sometimes.

TI code

If you are looking for an approach with formulas in Turbo Integrator (that you can easily port to rules), consider: 

# As an example, just to get the message across
vYear = 2015;

# Method 1
If( MONTH( DATE( DAYNO( NumberToString( vYear ) | '-02-28' ) + 1 ) ) = 2 );
   vLeapYear = 1;
Else;
   vLeapYear = 0;
EndIf;

# Method 2
If( ( MOD( vYear, 400 ) = 0 ) % ( ( MOD( vYear, 4) = 0 ) & ( MOD( vYear, 100 ) <> 0 ) ));
   vLeapYear = 1;
Else;
   vLeapYear = 0;
EndIf;

# Or shorthand notation
vLeapYear = If( MONTH( DATE( DAYNO( vYear | '-02-28' ) + 1 ) ) = 2, 1, 0 );
vLeapYear = If( ( MOD( vYear, 400 ) = 0 ) % ( ( MOD( vYear, 4) = 0 ) & ( MOD( vYear, 100 ) <> 0 ) ));

# But probably the best solution is with an attribute
vLeapYear = ATTRS( 'Cmn_Year', NumberToString( vYear ), 'Leap year' );

Method 2 will sound familiar to you, for method 1 you need a good portion of lateral thinking. In fact, we ask ourselves if the month of the day after February 28 for a given year, is 2 (February) or not (March in that case). If 2 (February) it is a leap year.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links