Ytd & Ltm

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

  • 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
  • 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