BusinessObjects Board

Predefined Filter for MTD and YTD

building semantic layer
#1

Hi Experts,

I’ve been trying to create these pre-defined filters capturing different time periods and keeps creating errors!!!

  1. MTD (First of the month till today)
  2. YTD (January till today)

I am using BOXI SP3.2 with SQL 2005.

I used “BETWEEN
convert(datetime,convert(varchar,dateadd(mm,-12,dateadd(dd,-datepart(dd,getdate())+1,getdate())),103),103)
AND
convert(datetime,convert(varchar,dateadd(dd,-datepart(dd,getdate()),getdate()),103),103”

Any help would be much appreciated.

Thank you

Jamesy121 (BOB member since 2009-05-04)

#2

Jamesy,

Have a look at this topic.

Regards,
Mark

#3

YTD:
Try this, it might work

Table.Date  BETWEEN '1/1/'+ Cast(Year(GetDate()) As Varchar(4))
AND GetDate()

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

#4

The best approach is to define a range table in your database that will allow you to pick the right date range for YTD or MTD. You can check out my post at http://howtobi.wordpress.com/2010/11/05/how-to-perform-ytd-or-any-period-to-date-design-in-the-universe/

hbanerjee :singapore: (BOB member since 2010-08-17)