# Ytd & Ltm

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # Ytd & Ltm

How can I handle YTD (year-to-date) information and LTM (last-twelve-months) in a excel reporting sheet.

I have dimensions for Year, Month (Actual/Budget) and I'm trying to see only ytd information, for example in March I wanna see Jan+Feb+March comparison against budget Jan - March. In August, Jan-August etc.

And how about LTM formulas? where I need to see LTM from August'06 - July'07.

Is it possible to have the summation done in another dimension, or do I need to use excel formulas to handle it?

Anyone has an idea?
• # RE: Ytd & Ltm

The YTD should be straightforward. Just consolidate the months. I know it adds a level to the months dimension but it's easier in the long run rather than manipulating your spreadsheet all the time.

I've set up my dimension like this:

Full Year
-Q1
--Jan
--Feb
--Mar
-Q2
--Apr
--etc
YTD P2
-Jan
-Feb
YTD P3
-Jan
-Feb
-Mar
YTD etc

LTM would definitely be trickier since this would require the dimension to change each month. I would probably set up something manually in Excel that calculates the last 12 months based on the current month. Maybe set up an hlookup table that looks at the current month and populates columns with the last 12 months.

J
• Thanks good idea, already implemented and it works perfectly.

If you have a better idea how to handle LTM, please let me know.

Thanks again.
• # month file with YTD

import it and try it
Files
• month.txt

• # Warning - Behaviour

Be careful when totalizing a dimension and want to use copy & like.

If you place 2 different total groups in a dimension and one of the totals references some elements referenced at the other total, Copy & Like wouldnÂ´t work as you expect.

ItÂ´s not the same:

Jan
Feb
Mar
.
.
.
TotalYear
YTD1
Jan
YTD2
Jan,Feb
YTD3
Jan,Feb,Mar

than

YTD3
Feb,YTD2
YTD2
Jan, YTD1
YTD1
Jan

Copy an Like will works when there is only one total that reference all elements as a parent. The first example wonÂ´t splash a Copy nor a Like comand but a simple splash.

A simple test with the Demo database is to add another TotalYear to the months dimension and Copy Actual;2007 to Actual;2009