Selecting Date Range w/Persistent Variables?

[#1]

The objective is to get a dynamic date range that goes back 13 weeks (91 days) from today's date, and to use that functionality in order to select the corresponding records in a field. I'd rather the users not be required to calculate what the date was 91 days ago each day in a calendar object, either. What I'm looking for is a way to do it via a load/where statement or just a simple button or bookmark.

I have a field containing a list of dates (update_date). The qvw pulls data from a consistently updated SQL database, so there's always a value for today's date. Is there a way to use the expressions

Today()-0

and

Today()-91

easily to achieve this? Or is there another way to do this? I've spent ~30 hours the past three days browsing this forum but have only found solutions where the user must manually enter the dates.

[Miguel_Angel_Baeyens]

Hi,

If what you want to do is make the selection simpler for the user, by means of a button, for example, create a button, go to its properties, Actions tab, add a new action type Selection, Select in Field, set update_date as field and

=Date(Today() -91)

As the search string. Make sure using a text object that this function returns in the same format that your update_date field is. You can also set this action to be triggered when the document opens (Settings menu, Triggers tab, On Open event) or when a sheet is activated (right click on any empty space of the current sheet, Properties).

If you need to load only those records where update_date equals to that date, you can do that using a variable:

LET vDate = Date(Today() -91);
Data:
LOAD *;
SQL SELECT *
FROM Table WHERE update_date >= '$(vDate)';

Provided as above that the update_date and the result of '$(vDate)' are in the same format.

Hope that helps.

Miguel

[#3]

Thanks a lot for the example of the correct syntax, that's one of the things that trips me up learning this language. The button-search-string method doesn't work, though.

The update_date field has the default date format (M/D/YYYY h:mm:ss TT) and I haven't changed the search string you provided either. Should I override the document settings for the date format in the update_date field?

[Miguel_Angel_Baeyens]

Hi,

Instead of changing the document properties to read the field with a new format, I'd rather change the search string to match format in the action

=Timestamp(Today() -91, 'M/D/YYYY h:mm:ss tt')

Hope that helps.

Miguel

[#5]

Thank you, I actually found a way to do it via a bookmark. I included a search box and bookmarked the results of these searches:

=update_date>now()-7 

=update_date>now()-91

Works beautifully.