Live to Date Consolidations for Year and Month

[damientaylorcreata]

Hi Guys,

Background to Question
I have created an inventory cube within TM1. My cube contains various dimensions including (Item,Warehouse,Transaction Type, Year, Month and Day, etc)... This allows a user to see what the balance on hand was at any point in time. This cube will work well for Movements and Stock on Hand for the current day. As the user would simply select the 'All Years' consolidation of the Year dimension and 'All Months' consolidation of the Month dimension. However what if I wanted the Stock on Hand for March 2007. In order to acheive the Stock on Hand for March 2007, the user would need to select the '2007 LTD' consolidation which would include all years up to and including 2007. Which would give the total up to and including 2007, however if they select March it would only total up the March movements for each year up to and including 2007.

Question
Therefore is there a way in TM1 for me to say if a consolidation in the Year dimension to change the Month dimension to 'All Months' for each year except for the top level year in which should only show me up to the end of March?

For example if '2007 LTD' is chosen from the Year dimension and March is chosen from the Month dimenion and assuming that the data in cube only goes back to 2004. To say that for years 2004,2005 and 2006 to use the 'All Months' consolidation from the Month dimension, but for 2007 to use the selected element (which is March) from the Month dimension.

My Attempt
I started playing around with the rules for the cube and did the following:

['Q1','2007'] = if(DIMNM('Year', DIMIX('Year',!Year)) @= '2008 LTD',['Full Year'],DIMIX('Month', !Month));

What I was trying to achieve was to say if the '2008 LTD' consolidation is chosen, simply make the 'Q1', '2007' eqaual to the 'Full Year' figure.

This would mean that if select Q1 as the month and "Full Year" as the year, it would mean that 2007 would really return the figure for Full Year and then add this to the Q1 figure for 2008. However it does seem to be working, but instead just returns the value of 2008 only. So I will have to keep plugging along, however if any body has any ideas, I would love to hear your thoughts.

Thanks heaps!

[Steve Vincent]

Not sure how your structure is, but would it be as easy as a hierarchy change?

Code: Select all

All Months
   End of Jan
      Jan
   End of Feb
      End of Jan
      Feb
   End of Mar
      End of Feb
      Mar
   End of Apr
      End of Mar
      Apr
.
.
.

That would allow you to pick a year and "End of" value that would consolidate the data in the way i think you are aiming for.

[damientaylorcreata]

Yes, this is what I plan to do for the Month dimension. However if I choose he 2007 LTD Year consolidation. Years 2005 and 2006 need to use a full year and 2007 would need to use the chosen Month consolidation.

For example if I choose the following elements:

Year Dimension = 2007 LTD (which includes 2005,2006,2007)
Month Dimension = 'End of March' (which includes Jan, Feb and Mar)

To get a Stock on Hand balance, Tm1 would need to use 'All Months' for 2005 and 2006 and then use Jan, Feb and Mar for year 2007. This would give us the live to date figure for how much stock is on hand at the end of March 2007.

Do you know of a way to achieve this?

[lotsaram]

I may have misunderstood your intent but why load only stock movements? Makes much more sense to also load stock balances as inventory balances are usually what people are interested in.

This will be much easier for users to navigate. The only issue for you to get used to would be that YTD and LTD consolidations would then be erroneous for stock balances (unless you put in a C level rule to make the consol equal to the last N child.)

[Steve Vincent]

Yes, this is what I plan to do for the Month dimension. However if I choose he 2007 LTD Year consolidation. Years 2005 and 2006 need to use a full year and 2007 would need to use the chosen Month consolidation.

For example if I choose the following elements:

Year Dimension = 2007 LTD (which includes 2005,2006,2007)
Month Dimension = 'End of March' (which includes Jan, Feb and Mar)

To get a Stock on Hand balance, Tm1 would need to use 'All Months' for 2005 and 2006 and then use Jan, Feb and Mar for year 2007. This would give us the live to date figure for how much stock is on hand at the end of March 2007.

Do you know of a way to achieve this?

My finance days come back to haunt me - Opening and Closing Balances.

Code: Select all

All Months
   End of Jan
      Opening Balance
      Jan
   End of Feb
      End of Jan
      Feb
   End of Mar
      End of Feb
      Mar
   End of Apr
      End of Mar
      Apr

The Opening balance for 2009 would be the value from [2008],[End of Dec]. Use a TI to transfer the values from closing to opening and it should do what you are looking for.

[damientaylorcreata]

Great Idea! Yes, I will create the extra Month elements (e.g. End of Feb, End of March, etc) and then simply add to my TI process to add the closing balances into the appropriate Year, End of Month data points.

Thanks guys, very helpful!