Dates, Subsets - MTD and YTD design

Post Reply
TM1Newbie
Posts: 5
Joined: Sun Mar 06, 2011 11:56 am
OLAP Product: TM1
Version: 8.4
Excel Version: 11

Dates, Subsets - MTD and YTD design

Post by TM1Newbie »

Hi

I'm using PALO to prototype a daily cube that will eventually to go into TM1. Working on ETL and testing the accuracy of the result My Palo version is little old as the last of the community edition using just an Excel-In (v 3.1?)

I am after some ideas on how to do a month to date (MTD) and a year to date (YTD) in a single date dimension without consolidated nodes (to numerous to build and may not always be needed). MTD and YTD need to dynamic enough so I could put in date and have to sum back to the beginning of the month (MTD) or the financial year (YTD which for me is 1 July). Also wanting them to do comparatives so that I can do Last Year YTD to same date as this Year to Date (and ignore the dates yet to come).

I understand the concept of subset but can't seem to get it work (which would seem the easiest way to do this)

Alternatively is there dimension I need to add (which can loaded in CSV file) which might help makes this possible?

Cheers

Sam
ardi
Community Contributor
Posts: 148
Joined: Tue Apr 02, 2013 1:41 pm
OLAP Product: tm1, cognos bi
Version: from TM1 9.4 to PA 2.0.9.6
Excel Version: 2010
Location: Toronto, ON

Re: Dates, Subsets - MTD and YTD design

Post by ardi »

In TM1 I would write a Rule to do that. So in your Measures dimension assuming you have 3 ELements: Daily Amount, MTD Amount and YTD Amount

In your Data DImension, you need to have 2 Attributed: Prior Date and Next date

Then in Order to Calculate the MTD Amount you need to write a Rule like below:

['MTD Amount'] = N: DB ( 'Cube_Name' , !Dim1, !Dim2, .... !DimDate, 'Daily AMount' ) + IF ( First~Day~Of~Month~Condition , 0 , DB ( 'Cube_Name' , !Dim1, !Dim2, .... ATTRS('DimDate',!DimDate,'Prior Date'), 'MTD Amount' ) ) ;
Ardian Alikaj
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Dates, Subsets - MTD and YTD design

Post by Steve Rowe »

TM1Newbie wrote:Hi

I am after some ideas on how to do a month to date (MTD) and a year to date (YTD) in a single date dimension without consolidated nodes (to numerous to build and may not always be needed). MTD and YTD need to dynamic enough so I could put in date and have to sum back to the beginning of the month (MTD) or the financial year (YTD which for me is 1 July). Also wanting them to do comparatives so that I can do Last Year YTD to same date as this Year to Date (and ignore the dates yet to come).

Sam
[Edit : Just realised I somehow ended up in the Jedox forum so I guess you may not be able to do some of the things below in Palo, if so then Palo doesn't make sense as a place to prototype your TM1 builds....]

I think that you are heading down the wrong path, your two objections to doing this with consolidations are really non-issues.
1. Too numerous - Build them with a TI and add structure too keep them nice and tidy.
2. Not needed - Should have no system impact and if you really don't want them then you can delete the non-relevant ones in the TI you wrote in 1 above.

If you want "special" MTD and YTD references for the current month then again build these with a TI and schedule it to run once a month.

The reasons you should build with a consolidation are
1. Performance - Rules are slower than consolidations.
2. Feeding - Feeding a ruled value whose source cells change frequently will be significant challenge, probably requiring rule recycling / restarts. You'll probably hit feeder stack issues on the YTD as well.
3. Future Proofing - Having all MTD / YTD available increases the utility of the system significantly, versus just having specific values for the one requirement you have now.

Don't do it!
Technical Director
www.infocat.co.uk
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dates, Subsets - MTD and YTD design

Post by lotsaram »

I agree wholeheartedly with my learned colleague. In a dimension with date granularity consolidations are THE way to implement MTD & YTD calculations & reporting. I would not for a second entertain any other way of doing it.

Consolidations will be by far the best performing solution and once created require no maintenance. In terms of the creation this can be done with TI and is not a huge amount of work. In a well organized time dimension having additional MTD & YTD consolidations will not impact users and will not impact or have any measurable impact on system performance as C elements are only calculated on demand.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
TM1Newbie
Posts: 5
Joined: Sun Mar 06, 2011 11:56 am
OLAP Product: TM1
Version: 8.4
Excel Version: 11

Re: Dates, Subsets - MTD and YTD design

Post by TM1Newbie »

Thanks

Have moved to TM1 but still have a lot to learn about TI.
Post Reply