BusinessObjects Board

Formula to find First Day of Previous Year

Hi All,

At the report level I need to find 4 dates.

  1. First Day of current Year
    which I calculated as
=RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate())+1)
  1. First day of Previous Year

How to obtain this???

  1. YTD Date for Current Year which is calculated as follows and always covers full weeks :
  • the date the report is refreshed ,
  • take the following Sunday ,
  • deduct 2 weeks .
    Example :
  • if the report is refreshed say on Tuesday 28th Sep 2010 ,
  • the following Sunday is 3rd Oct 2010 ,
  • deduct 2 weeks , and get 19th Sep 2010 .

Able to calculate it as

=LastDayOfWeek(CurrentDate())-14 
  1. YTD Date for Previous Year (This is the exact date as the YTD Date for Current Year but 1 year less i.e. 19th Sep 2009 as per the example shown above)

How to obtain this?

It would be extremely nice if somone can guide me as to how I can obtain -->First day of Previous Year & --> YTD Date for Previous Year

Also, if someone can propose better ways of obtaining

  1. First Day of current Year & 3. YTD Date for Current Year, they are most welcome.

Thanks to all.


ArrowHead (BOB member since 2004-09-09)

Go thru this you might try to implement it at the universe level…

For Firstday previous year try this:

Create a Variable PYLD as :

PyearLD=RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate()))

Then create a variable PYFD as :

PyearFD=RelativeDate(PyearLD,-DayNumberofyear(PyearLD)+1)

Let me know…


nitin_gons :india: (BOB member since 2009-05-26)

Thanks a ton nitin_gons.

I do not have the ‘privilege’ of tinkering with the Universe, so has to be at the report level.

Thanks for your suggestion.


ArrowHead (BOB member since 2004-09-09)

Hey…my pleasure…

But you should ask your universe designer(or the Administrator) to create the objects at the universe level…I am sure you would require these Variables in other reports as well…why duplicate your efforts?

BTW you can just call me “Nitin” :wink:


nitin_gons :india: (BOB member since 2009-05-26)

HI

have look at this dave blog

trunc(trunc(sysdate,’YYYY’)-1,’YYYY’)


keka (BOB member since 2010-06-14)

I hope you have implemented the solution i have mentioned…coz it’s at the report level…
The universe level solution i had mentioned was for the link i had provided…


nitin_gons :india: (BOB member since 2009-05-26)

This is a universe level solution, which the OP does not have access to.