Rolling 12 Months having separate Month & Year Dimension

[winsonlee]

Hi all

I have two separate dimension for Months and Year. Months dimension have two attribute call MonthNo and MonthYear. I am using rule to populate which year it should be using to the MonthYear attribute. Eg now it is october, so Jan to Oct will be using 2012 sales data, and Nov-Dec will be using 2011 sales data.

I would like to get some feedback if there is any better way of doing this ?

Year Dimension

Structure
- 2011
- 2012
- Rolling 12 Months

Months Dimension

Attribute
- MonthNo
- MonthYear

Structure
-Jul
-Aug
-Sep
-Oct
-Nov
-Dec
-Jan
-Feb
-Mar
-Apr
-May
-Jun

Code: Select all

skipcheck;
['MonthYear'] =S: IF(ATTRN('Months',  !Months, 'MonthNo') <= Month(Date(NOW)), TIMST(NOW, '\Y'), TIMST(DAYNO(Dates(TIMVL(NOW, 'Y') -1,1,1)), '\Y'));

Sales Cube

Code: Select all

skipcheck;
['Rolling 12 Months'] = N:DB('Sales Cube', !test - Customer, !Months, ATTRS('Months', !Months, 'MonthYear'), !test - Measures);

feeders;
['All Years'] => ['Rolling 12 Months'];

[David Usherwood]

a Build YTD rollups for the months eg Feb YTD = Jan + Feb
b Calculate the rolling 12 months as
Current YTD +
Prior Full Year -
Prior YTD
Remember consolidations (plus or minus) are 100x faster than rules and don't need feeding.

[Steve Rowe]

Morning,
I guess it depends if you want to see the 12 months all in a line together or if you just want the results of the full year.

If you just want the result then I'd use Davids method.

If you want to see the 12 months for reporting purposes then I'd do something like what you have done but not the same.

For me your logic is too automated, I'd like more control over when the current month flips over since I only want it to flip over when the actual data is ready.

I'd also store the string information about which year to use in a cube since calculated string info is not cached. The rules should evaluate faster without a dependancy on a string rule. Not sure if this would be visible to users or not.

HTH

[winsonlee]

I would need to produce a report according to individual month eg

Oct (2011) Nov (2011) Dec (2011) Jan (2012) ... etc ..
Rolling 12 Months

[winsonlee]

Will this work more efficient compare to my first approach ? I have a dimension call Time with the following Structure. I hard coded the year into the rules and it works. But if i replace it with ATTRN('Time', !Months | ' (' |!Year|')', 'Year') it doesn't work. Anyone notice if I did anything wrong with the concatenation ?

Time Dimension
- Jul (2012)
- Aug (2012)
- Sep (2012)

Attribute
- Month
- Year

Sales Cube

Code: Select all

['Rolling 12 Months'] = N:IF(ATTRN('Time', !Months | ' (2012)', 'Year') = TIMVL(NOW, 'Y') & ATTRN('Time', !Months | ' (2012)', 'Month') <= TIMVL(NOW, 'M'),
			DB('Test Cube', !test - Customer, !Months,  '2012', !test - Measures),continue);

['Rolling 12 Months'] = N:IF(ATTRN('Time', !Months | ' (2011)', 'Year') = (TIMVL(NOW, 'Y') - 1) & ATTRN('Time', !Months | ' (2011)', 'Month') > TIMVL(NOW, 'M'),
			DB('Test Cube', !test - Customer, !Months,  '2011', !test - Measures),continue);

[Sreddy]

Hi All,
I am also working on a report to show last 12 Months data from selected Month and Year with a TM1 Cube. I have separate Year & Month dimension and i am creating this report in cognos report studio. i have to show multiple measures in rows and last 12 months in columns.my approach ro get this is as follows
I created 2 value Prompts for year and Month and then calculated Prior year with IF ..THEN ..ElSE with year prompt(if ?year? = 2010 then ..)
calculated Month YTD and Month YTG in same way by using ?Month? Prompt.And Total of last 12 months as Tuple (Current measure,Prior Year, Month YTG)+Tuple(current measure,year,Month YTD). it is working fine for totals. but there are 2 measures in rows where it default shows average. my caclulation for TOtal is not working in this place as instead of x/12 it is calculating as a/months in prior year+b/months in year.

can any one tell me if there is an alternate way to get last 12 months in report.

[LanceTylor]

There is an alternative. You could include a third time dimension in your cube to calculate your rolling 12 months or any other relative time you may require.

You would need to create/define the rules for each relative time period but it would provide the flexibility to select your month/year and see the rolling 12 months (in the third time dimension). As this cube would likely be quite rule heavy I would suggest creating a Reporting Cube and transfer all the data via TI which would improve performance.

This will be work well with BI as you would be able to Prompt on your month and year and use your third time dimensions (relative time) in your actual report.

Here is a link that can provide more info on how to set it up (as well as potential alternatives)
http://www.tm1forum.com/viewtopic.php?f=3&t=8150

Good Luck
Lance

[BariAbdul]

Hi Sreddy ,There is great post about relative time design in Wim's Blog,Think worth going through:
http://www.wimgielis.be/ Thanks

[Sreddy]

Thanks everyone for your time and prompt response. I am purely a cognos developer but not a TM 1 person.i develop reports in Cog nos using TM 1 cubes. I am looking for a work around on report level for this issue. but i will forward these suggestions to our TM 1 cube designer and see what she comes up with.

[lotsaram]

Thanks everyone for your time and prompt response. I am purely a cognos developer but not a TM 1 person.i develop reports in Cog nos using TM 1 cubes. I am looking for a work around on report level for this issue. but i will forward these suggestions to our TM 1 cube designer and see what she comes up with.

I guess the point is that sometimes (even most times) the issue of relative time reporting and calculations is much better solved in the design of the cube itself than after the fact work arounds in the reporting layer.

With regards to the design framework that Lance linked to I have since set this up a few times specifically for the purposes of optimizing TM1 cubes for Cognos BI reporting and this is how I would always build cubes when there is a requirement for reporting in Cognos BI. It just makes things far simpler to set up in report studio, and lets face it, it is complicated enough already creating stuff in the studios.

[Patrick Gr]

Hello,

I jump over this old post as I found it similar to my case. and It is the first time I use feeders on my cube, and find it pretty difficult to apply.

The cube contains 5 dimensions :
Year :
2015
2016
…
2019
Month :
Jan
Feb
Mar
…
Dec
Phase :
M
M-1
YTD M
YTD M-1
12 Months
Products :
Product A
|-> Article Aa
|-> Article Ab
|-> Article Ac
|-> Article Ad
Product B
|-> Article Ba
|-> Article Bb
|-> Article Bc
|-> Article Bd
…
Indicators :
Sales
margin
%margin

I’ve set a rule calculating rolling 12 months :

['12 months’]= DB('mycube', !4 year ,'December','YTD N-1',!PRODUCT, !INDICATOR)
- DB('mycube', !4 year,!Month ,'YTD N-1',!PRODUCT, !INDICATOR)
+ DB('mycube', !4 year,!Month ,'YTD N', !PRODUCT, !INDICATOR);

I get the correct calculation. But when adding this feeder, data is completely biaised

['YTD N-1']=> ['12 months' ];
['YTD N']=> [‘12 months' ] ;

I don’t understand what’s wrong ? would you please help me set/understand the right feeders.