Product:
Cognos TM1 10.1.1 (version in cmplst.txt file is RTM-10.1.7000.91-0)
Update TM1 version is RTM-10.1.7101.108-0
Windows 2008 R2 Datacenter server, Service Pack 1
Problem:
Get the last day in each month to a value. You have in a dimension data, that only contain year and month, but you need to export it as year-month-day. And you want to have the day to be the last day in the each month. How do you find the last day in a month?
Solution:
You have a dimension called S2.PeriodMonthly that contains the hierarchies of the year – quarters – months. Below the structure of the Dimension.
2011
2011Q1
201101
201102
201103
2011Q2
201104
201105
201106
Add to this is an alias called S2.Name where we want the information to be in this format
2011
2011Q1
2011-01-31
2011-02-28
2011-03-30
Select Display Control Objects from the VIEW menu in TM1 Architect.
Open the control cube for }ElementsAttributes_S2.PeriodMonthly and right click and select edit rule.
Enter below code text to create the rule, which will update the Alias with the Correct Day in the month for the values entered into the dimension. This rule is automatic processed when values are entered into the dimension.
#———————————————————————————
# RULE 1
# Set the last day in the month
#———————————————————————————
[‘S2.Name’] = s:
if( ellev(‘S2.PeriodMonthly’,!S2.PeriodMonthly) = 0,
#——- 31 DAYS —————————#
if(
subst(!S2.PeriodMonthly,5,2)@=’01’ %
subst(!S2.PeriodMonthly,5,2)@=’03’ %
subst(!S2.PeriodMonthly,5,2)@=’05’ %
subst(!S2.PeriodMonthly,5,2)@=’07’ %
subst(!S2.PeriodMonthly,5,2)@=’08’ %
subst(!S2.PeriodMonthly,5,2)@=’10’ %
subst(!S2.PeriodMonthly,5,2)@=’12’,
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-31′,
#——- 30 DAYS —————————#
if(
subst(!S2.PeriodMonthly,5,2)@=’04’ %
subst(!S2.PeriodMonthly,5,2)@=’06’ %
subst(!S2.PeriodMonthly,5,2)@=’09’ %
subst(!S2.PeriodMonthly,5,2)@=’11’,
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-30′,
#——- 28-29 DAYS —————————#
if(subst(!S2.PeriodMonthly,5,2)@=’02’,
if( mod (numbr(subst(!S2.PeriodMonthly,1,4)),4)=0,
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-29′,
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-28′
),
STET
)
)
),
STET
)
[‘S2.Name’] = s:
if( ellev(‘S2.PeriodMonthly’,!S2.PeriodMonthly) = 0,
Above will set the alias S2.Name to have this new value only if it is the lowest dimension, and not a consolidated value. ELLEV returns the level of an element within a dimension.
If S2.PeriodMonthly have a value at the lowest level (=0) then it will do the coming commands.
#——- 31 DAYS —————————#
if(
subst(!S2.PeriodMonthly,5,2)@=’01’ %
Above will check if the value for dimension S2.PeriodMonthly has the letters 01 in the last part.
So it will check 201101 that the 2 characters with start of character 5 in the word are equal to string 01. In that case it will move to next step.
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-31′,
Above will create the value 2011-01-31 for the alias S2.Name.
Subst takes value from dimension and from start position and then number of characters, so this subst(!S2.PeriodMonthly,1,4)| will give ‘2011’. The pipe sign is the add function to add the next string – in this case ‘-‘ that will give the result ‘2011-‘. The next subst(!S2.PeriodMonthly,5,2) will add the characters from position fifth and two characters, in our example that is ‘01’.
Then |’-31′ will add ‘-31’ to the end of the string value that we put inside S2.Alias.
#——- 28-29 DAYS —————————#
if(subst(!S2.PeriodMonthly,5,2)@=’02’,
if( mod (numbr(subst(!S2.PeriodMonthly,1,4)),4)=0,
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-29′,
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-28′
Above part will check if the year is a leap year, by function mod 4 that will give every forth year.
MOD returns the remainder of dividing a number by a divisor.
(This function will not work for the year 3000, but otherwise be correct.)
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-29′,
subst(!S2.PeriodMonthly,1,4)|’-‘|subst(!S2.PeriodMonthly,5,2) |’-28′
In TM1 Rules, the else command is the comma, so in case it is a leap year we will set the value ‘29’ and if there is not a leap year ‘28’.
You can use it like this, test on a values lenght, and if to long replace it with this value.
v23 = pPeriod;
if (long (v23) = 6);
v23 = attrs ( ‘S2.PeriodMonthly’, v23, ‘S2.Name’) ;
endif;
Above place input parameter pPerid in variable v23, then we test if it is exact 6 characters long, and in that case we use the value for S2.PeriodMonthly alias S2.Name when the v23 variable match the dimension content in S2.PeriodMonthly. ATTRS returns a string attribute for a specified element of a dimension. Gives that the v23 contain the value in S2.Name if the v23 is exact 6 characters long.