Dates, Subsets - MTD and YTD design

[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]

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' ) ) ;

[Steve Rowe]

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!

[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.

[TM1Newbie]

Thanks

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