Year to date comparison with previous year.

Everyone will once want to compare values from the beginning of a year until ‘a certain date’ with values from the exact same period but then from the previous year until ‘that certain date a year ago’. I found this youtube video where RimGeurts explains how to do this with BusinessObject Web Intelligence.

Year to date WebI

The screencapture is an excellent ‘how to’ guide to make a year to date (YTD) comparison based on an input date field. In this post I make an addition in case you don’t have a date but a month- or fiscal period code (like ‘201106’, or ‘FP201012’).

In your query create a prompt for the dimension, in this case ‘Fiscal period code’:

In the report we can use the input of the user to calculate the year and the month. Create a variable for the chosen year: [vStatementYear]

And for month [vStatementMonth]

=Substr(UserResponse("Fiscal Period Code");7;2)

Next we’ll create variables to calculate the year and month that belongs to the measures:

[vDataYear]

=Substr([Fiscal Period Code];3;4)

[vDataMonth]

=Substr([Fiscal Period Code];7;2)

Once we’ve got these four variables we can easily create the variable measure [Cost YTD]

For the measure that displays the last year to date value, duplicate the variable [Cost YTD]

Subtract 1 from vStatementYear to get the last year:

=[Cost Amount] Where([vDataYear] = [vStatementYear]-1 And [vDataMonth] <= [vStatementMonth])

In my case I got a data type error; the years and month are obviously type character due to the substring function. This can be easily solved by wrapping the function in the ToNumber function:

=ToNumber(Substr(UserResponse("Fiscal Period Code");3;4))

For the current month create a variable Cost CM:

=[Cost Amount] Where([vDataYear] = [vStatementYear] And [vDataMonth] = [vStatementMonth])

 

In the example from the video given above, the same is done but then with the following variables:

vDataMonth
=MonthNumberOfYear([Day])
vDataYear
=Year([Day])
StatementMonth
=MonthNumberOfYear(ToDate(UserResponse("Statement Date:");""))
vStatementYear
=Year(ToDate(UserResponse("Statement Date:");""))
Linkedin Twitter Facebook Stumbleupon Tumblr Email

12 thoughts on “Year to date comparison with previous year.

  1. muhamamd patel

    hi i have a same problem however. im not using any prompts because amount object is a measure object. i need to have that value to show previous year december where the amount is ytd.

    please any help would be highly appreciated.

    Reply
  2. Paul Berden

    Hi muhamamd, in my example I’m using a measure object for the YTD amount. If you don’t want to use a prompt, then you might want to have a relative date object in your universe to filter on the current year to date: link

    This link might help you as well to retrieve the previous year to date.

    Reply
  3. sunil

    how do u get previous ytd if u filtered the current fiscal period ?? i mean it works perfect when we filter current ytd at query level but what abt previous year . do we get the qty values for that ??

    Reply
    1. Paul Berden

      Hi Sunil, you’re right, that will not work. In the example explained above I used a very small set of data and retrieved everything because the operators are [less than or equal to] prompt AND [greater than or equal to] prompt.

      Reply
      1. Sam

        Great going Paul. I do have one doubt, in the video RimGuerts calculated the YTD figures based on the month so it would not exactly match the actual YTD figures, I mean if we provided 17th August XXX as the statement date and then if there were transactions made in the rest of the month say on 20th August and so on, then these figures would also be taken into calculation.

        Reply
        1. Paul

          Hi Sam, that’s true! This implementation has more disadvantages. I’m planning to write a new article with alternative solutions for YTD figures based on BI4 version.

          Will have to find some spare time to get that done…

          Reply
  4. Pingback: Year to Date selection in SAP BI4 - Paul Berden

  5. Shawn Graham

    I created all the vStatementYear, VStatementMonth, VDateYear, VDateMonth then when i go to put the formula

    [Cost Amount] Where([vDataYear] = [vStatementYear]-1 And [vDataMonth] <= [vStatementMonth])

    It gives me an error on vStatementYear. I change the formula to be ToNumber and then I get error for VDateYear

    Reply
  6. Hartford21

    When your living alone you will want to take extra precautions to stay safe.

    They can get your clutter up and off of the ground.
    Also look for the outlets for your cable and phone jacks.

    Reply
  7. Duane

    I have a question, I am needing to prompt a report by month but not by year. Can this be done? For example I need a list of all x that happened in February of any year after year y.

    Reply
  8. commissary coupons

    Hey Gary! Let me guess…you don’t work!! Spread the wealth??? So the guy that works hard, goes into debt to be successful, spends 20 years getting an advance degree, should give it all away in the form of taxes?? You don;t get it! That’s why corporations are leaving and wealthy individuals are leaving. How about lower the tax rate which will keep companies here, more companies actually paying US taxes, more companies hiring Americans, more jobs, more people working, more people paying taxes, more revenue for the government!

    Reply

Leave a Reply

Your email address will not be published.