4 Replies Latest reply: Jan 29, 2012 3:56 PM by David Nelson RSS

    Selecting Date Range w/Persistent Variables?

      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.

        • Re: Selecting Date Range w/Persistent Variables?
          Miguel Angel Baeyens de Arce

          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