Deriving an Element from an Alias in TM1

Today I needed to look up an element name from an alias. The solution involved combining the use of Dimix and DimNm.

Problem

The situation was that I have a System Settings cube which derives the date and subsequently the year, month and day from the date. The month is string and in the form “03”. I wanted to add an element that is also string that shows the month as a three character field, like “Mar”, for example. I have a Month dimension with Jan to Dec in it and alias’ for “03” and “3” etc.

So my challenge was to lookup the Month dimension and find the value of the month in the form “03” and then return the month in the form “Mar”.

Solution

The solution to this problem was to use a combination of Dimix and DimNm as follows (along with the SubSt and Today functions as well):

['Current Month (mmm)', 'String'] = S: DIMNM( 'Month', DIMIX ('Month',  SUBST(TODAY(1),6,2) ) );

So here ‘Current Month (mmm)’ is derived by getting the index number of the two character month number from the Month dimension and then converting that to the element name using DimNm.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.
This entry was posted in Rule Examples. Bookmark the permalink.