Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating Year to Date Filter

Status
Not open for further replies.

Emmy2368

MIS
Jan 16, 2014
9
GB
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.
 
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
 
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
 
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
[URL unfurl="true"]www.protiviti.com[/URL]
 
Thanks Dell. My apologies for the late response. Your first solution worked for me. Thanks for your assistance. Very much appreciated.

Emma
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top