Subroutine to Create Data Model for From/To Date Selection
It seems common to need to select an arbitrary date range by simply selecting a From and To date, such as with calendar objects. This seems to usually be handled through the use of variables either triggering selections on the main date field or through the use of set analysis referring to those variables.
Both approaches work and have their merits, but I tend to prefer data model solutions where practical. Henric Cronström and I'm sure others have pointed out that it could be handled in the data model through the use of two AsOf tables, one pointing forward in time and one pointing backwards. The problem with this is that the number of rows required is the square of the number of days involved. That's fine for shorter date ranges, but if you want to look at 30 years of data, for instance, that's more than 100 million rows. That's not practical.
However, we can greatly reduce that number if we also add two date key tables, following the idea I'd suggested here. A date key is either a single date, a month, or a year. So to link to three years of dates, we only need three values. With that approach, 30 years of data requires fewer than 2 million rows.
Here are a couple pictures to help show what's going on:
I wrote a subroutine to do all the hard work. Build your data model normally. Then call the subroutine, telling it the name of the existing date field, and what you want to call the new from and to date fields. It'll build the data structures to link everything together. Make selections in your new from and to date fields, and it'll link to all the dates in that range. I plugged it into one of my real applications, and it seemed to work properly without modification.
There's probably some cleaner way to write the subroutine. I don't write many subroutines, and I didn't have a lot of time to think about how this might be done more cleanly. Hopefully it's bug free, but I easily could have made a mistake. I'll correct any that are found.