×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Creating Year to Date Filter

Creating Year to Date Filter

Creating Year to Date Filter

(OP)
Hi,

Could you please assist. Currently using BO version 4.1 SP6

I have a task of creating a Year to LastMonthEnd filter for both current & previous year. Our financial year starts from 1st Sept. I'm trying to avoid hardcoding the date in the filter but to make it more dynamic.

DATE Between '2016-09-01 00:00:00.000' AND convert(varchar(10),dateadd(dd,-(day(dateadd(mm,-12,getdate()))-1),dateadd(mm,-12,getdate()-1)),102) + ' 23:59:59.000'
DATE Between '2017-09-01 00:00:00.000' AND convert(varchar(10),dateadd(dd,-(day(getdate())),getdate() ),102)+ ' 23:59:59:999'

I would appreciate your help on this.

Thanks in advance.

RE: Creating Year to Date Filter

I see this has been here a couple of months. Were you able to resolve the issue? If not, I have a couple of thoughts. What type of database are you connecting to?

-Dell

Senior Manager, Data & Analytics
Protiviti
www.protiviti.com

RE: Creating Year to Date Filter

(OP)
I wasn't able to solve the problem so I ended up hard-coding it. I'm still very interested on hearing a dynamic way of doing it. I intend to achieve this at Universe level rather than database level. I use SQL server 2012

RE: Creating Year to Date Filter

If you're on SQL Server 2012 or newer, I would do something like this in the universe (based on the current date):

Create a dimension called something like "Current Fiscal Year Start", which would look like this:

CODE

Case
  when Month(GetDate()) < 9 then
    DateTimeFromParts(Year(GetDate()) - 1, 9, 1, 0, 0, 0, 0)
  else
    DateFromParts(Year(GetDate(), 9, 1, 0, 0, 0, 0)
end 

I would then create another dimension called "End of Today" current date that looks like this:

CODE

DateTimeFromParts(Year(GetDate()), Month(GetDate()), Day(GetDate()), 11, 59, 59, 999 

If there are specific dates you want to filter this way, I would create a filter for each that uses these two called "<(DateDimension> Fiscal Year to Date".
[code]
@Select(Folder\MyDateField) between @Select(Folder\Fiscal Year Start) and @Select(Folder\End of Today)

None of these will validate in the IDT or UDT, but they will work in a query with no issues.

-Dell

Senior Manager, Data & Analytics
Protiviti
www.protiviti.com

RE: Creating Year to Date Filter

(OP)
Thanks Dell. My apologies for the late response. Your first solution worked for me. Thanks for your assistance. Very much appreciated.

Emma

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close