Did you know you can create a single hierarchy that contains all year-to-date values within an entire year?

As you know, TM1 is a consolidation tool which is designed to sum together a series of values.  There are many ways to create the consolidations using a combination of grand totals and sub totals.  This blog specifically addresses the year-to-date (YTD) hierarchy associated with a time dimension.

Many clients prefer to create a single hierarchy that contains all YTD values within an entire year.  The following image shows an example of this for a model that separates month and year as two separate dimensions:

This approach allows users to drill into the values one month at a time to show both the previous YTD value and the value for the month.  But what happens if you want to see all of the details associated with YTD-Dec?  If a user wants to see each of the 12 months and the total then the user is required to drill 12 times and also see a series of subtotals along the way.

Instead of creating a single hierarchy, Revelwood recommends that a series of YTD calculations are created.  Each consolidated total consists of a single consolidated elements with a series of base level values below it.  The YTD totals are then consolidated into a grouping-consolidation called “Total YTD”.

For example, a group called “Total YTD” will contain children called Jan YTD, Feb YTD, etc.  Jan YTD will consist of one base level element (Jan) and Feb YTD will consist of two base level elements (Jan and Feb).  The “Total YTD” element will never be used for reporting since it serves solely as a grouping element, but the other various YTD elements will allow user to click once to see all of the details within the total.

This approach shows the same subtotals as the initial approach while simplifying the drilling requirements.  As your users become more involved with ad-hoc analysis, they will appreciate the simplicity to dig into the details via a single click.

This method can also be expanded to use other time consolidations such as fiscal year (FY), ROY (rest of year) and TTM (trailing twelve months).

IBM Planning Analytics  is full of new features and functionality.  Not sure where to start?  Our team here at Revelwood can help. Contact us for more information at info@revelwood.com.  And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: GMT vs Local Time

IBM Planning Analytics Tips & Tricks: Captions

IBM Planning Analytics Tips & Tricks: Regions with Planning Analytics Rules