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.
