TM1 working days

  • Apr. 7, 2016
Example files with this article:
  • TM1 working days

    • Introduction

    TM1 has a number of functions with which you can do the most common calculations. What is missing, however, next to a function to replace strings of text for example, is (a.o.) functions to work with weekdays and working days. For instance, what is the 5th working day in every month ? While I know that you can fill in this kind of information once a year, I thought it would be interesting to have a look at TM1 rules to calculate it.

    Below, I present you a simple cube and its rules. The cube does not use feeders since there is very little interest in. I make abstraction of holidays. That could be a simple input field that you add yourself. I should still leave some of the work to my readers ;-) Also, I know that I could use Turbo Integrator to fill in the cells, and maybe that would have been easier and better. Nevertheless, I show you a solution using rules.

    A simple model

    At the top of the page you can download a zip file containing a few TM1 objects (rules, cubes, dimensions, attributes). Download it and add it to your TM1 server to play around.

    The cube has 3 dimensions:

    1. WorkingDay_Year
    2. WorkingDay_Month
    3. WorkingDay_Msr

    Here are the rules: 

    
['#'] = Stet;

['First day of the month (TM1)'] = N:
Dayno( !WorkingDay_Year | '-' | !WorkingDay_Month | '-01' );

['First day of the month (Excel)'] = N:
['First day of the month (TM1)'] + 21916;

['Day with # offset (TM1)'] = N:
['First day of the month (TM1)'] + ['#'] - 1;

['Day with # offset (Excel)'] = N:
['First day of the month (Excel)'] + ['#'] - 1;

# Assuming 1 = Monday, etc., 7 = Sunday
['Weekday of day 1'] = N:
Round( Mod( ['First day of the month (TM1)'] + 21915 - 0.5, 7));

['Weekday of day 1_String'] = S:
If( ['Weekday of day 1'] = 1, 'Monday',
If( ['Weekday of day 1'] = 2, 'Tuesday',
If( ['Weekday of day 1'] = 3, 'Wednesday',
If( ['Weekday of day 1'] = 4, 'Thursday',
If( ['Weekday of day 1'] = 5, 'Friday',
If( ['Weekday of day 1'] = 6, 'Saturday', 'Sunday' ))))));

['Weekday of working day #'] = N:
Round( Mod( ['Working day # (TM1)'] + 21915 - 0.5, 7));

['Weekday of working day #_String'] = S:
If( ['Weekday of working day #'] = 1, 'Monday',
If( ['Weekday of working day #'] = 2, 'Tuesday',
If( ['Weekday of working day #'] = 3, 'Wednesday',
If( ['Weekday of working day #'] = 4, 'Thursday',
If( ['Weekday of working day #'] = 5, 'Friday',
If( ['Weekday of working day #'] = 6, 'Saturday', 'Sunday' )))))));

# Main calculation
['Working day # (TM1)'] = N:
['Day with # offset (TM1)'] + Attrn( 'WorkingDay_Msr',                                     Trim( Str( ['Weekday of day 1'], 2, 0 )) | '_' | Trim( Str( ['#'], 2, 0 )),
                                     'Number of weekend days' );

['Working day # (Excel)'] = N:
['Working day # (TM1)'] + 21916;

    Note that I assume that Monday is weekday number 1, Tuesday is weekday number 2, … and Sunday is weekday number 7. The nested IF functions for weekday names could be replaced with a simple Dimnm to a separate dimension (that you do not use in the cube). That is all down to personal taste, just as adding an alias on the dimension WorkingDay_Month to have the full month name (January, February, and so on).

    The rules calculations not only show the readers how to deal with weekday numbers and English names, they also learn how to switch between serial numbers for dates in Excel and TM1. These are not equal, as you will probably know.

    Attributes to help us

    What will not be immediately clear, is the numeric attribute 'Number of weekend days' on the dimension WorkingDay_Msr. Looking at the screenshot above you will notice that in the measures dimension, I added a bunch of elements: 1_1, 1_2, 1_3, 1_4, … What is the purpose? The elements concatenate 2 things:

    1. The starting weekday of the month, i.e. the 1st day of the month is Monday, Tuesday, Wednesday, …, Sunday; 7 possibilities
    2. Which working day do we want? The working day 1, working day 2, …, 23. Every month has a maximum of 23 working days.

    Basically, for these combinations (161 in total, 7 * 23) we store in a numeric attribute the number of weekend days that we need to subtract. If the first day of the month is Wednesday and we want the 5th working day of that month, then my rules do this:

    1. Determine that day 1 is a Wednesday
    2. Add that 5 days (it's input by the user)
    3. Add the number of weekend days in the period from the 1st of the month, until the (1+5)th day

    That last bit of information is conveniently stored in the attribute on the measures dimension. I calculated the days in Excel once, and copy/pasted to TM1.




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links