Select date range from 2 separate date columns

[lbunnell]

I have 2 columns with dates representing the opening and closing of an item.  I have created 4 variables for 2 date ranges 1) OpenDate(vOpenStart, vOpenEnd) and 2) CloseDate (vCloseStart, vCloseEnd) to search with 2 sets of 2 calendar objects for selecting beginning and end dates along with a search button with the following expressions in each set of objects:

Open Date search button:
= '>=' & Date(vStartDate, 'MM/DD/YYYY') & '<=' & Date(vEndDate, 'MM/DD/YYYY')

Close Date search button:

= '>=' & Date(vCloseStart, 'MM/DD/YYYY') & '<=' & Date(vCloseEnd, 'MM/DD/YYYY')

The way it is set up now, I only get one or the other when selecting each one individually, or if I select both, it eliminates the records that don't have both a start and close within the date range. What I need to do is create a single search button to be able to select all of the records where the OpenStart date OR the CloseStart date fall within the date range selected.

Thanks,

Lawrence

[DavidFoster1]

I suspect that you need to look at creating your 2 calendars as data islands and then incorporate the selected values into set analysis functions to control your result set.

This is a good thread to read Conditional Queries with Data Islands

[lbunnell]

Could you explain further? The date fields are part of a larger dataset that contains a key field. If I create an islands of the calendar date, I don't understand how I would be able to use a set analysis that would show all the records that have key fields with open and close dates within the range if the island table doesn't know which records have associated key fields. Maybe, and quite possibly, I'm missing something.

[DavidFoster1]

Have you had a read of the thread I pointed to?

Data islands is an approach for complex (and potentially multi-fact) filters.

The basic approach is to create separate data island tables with your 2 lists of dates.

In your set analysis you then reference the data island values.

e.g.

=SUM({<Year="_YEAR">} Amount)

where Year is the year in the normal model and _YEAR is the data island.

[lbunnell]

Yes, I've read the article, but I don't see how that explains how to capture both the records that have Open Dates and/or Close Date within the date range. Whether I use an island or not, I still get only the records that have both Open Dates and Close Dates within the range - (not records where the Open Date is prior to the date range selected). In other words, if the date range is 1/1/2014 to 1/31/2014 and the Close Date is 1/1/2014, I'm not getting any records where the Open Date might be 12/15/2013.

I was thinking something like this, but this doesn't work either:

count({<[OPEN DATE] = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'} > + {<[CLOSE DATE] = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'} > }[LOAN NUMBER])

[lbunnell]

OK, I created the Date Island and this now works:

count(if(([CLOSE DATE]>=$(vStartDate) and [CLOSE DATE]<=$(vEndDate) ) or ([OPEN DATE]<=$(vEndDate) and [OPEN DATE]>=$(vStartDate)) ,[LOAN NUMBER]))

I get Open and Close dates within the selected date range of the island. However, I'd like to do this in a Set Analysis which I haven't figured out yet.

Thanks!