4 Replies Latest reply: Sep 14, 2016 11:43 PM by Sunny Talwar RSS

    Prior Month

    Todd Johnston

      Hello All,

       

      I have several SQL queries populating QVDs within Qlik Sense.  I am attempting to automate my apps so that at the beginning of every month a task reloads the data and brings in the most recent data.  Each SQL table has an 'AsOfDate' field that indicates the relative month of the data (eg 2016-08-01 is the most recent data in each SQL table.)

       

      What I am attempting to do is use a where clause in the Data Load Editor to target last month's data.  Currently, I have to go in each month and update the where clause to the most recent month.  (where AsOfDate = '2016-08-01';  I want to say there is a way to do it with today()-1, but I cant seem to make it work.

       

      Any help would be greatly appreciated.

       

      Thanks,

       

      Todd

        • Re: Prior Month
          Chris Deniziak

          Todd - Try and create a variable to generate a date like:

           

          LET vMinDate = Date(MonthStart(Today()-1),'YYYY-MM-DD');

           

          And then use it in your where clause:

           

          LOAD * From MyQVD.qvd(qvd)

          Where LoadDate >= '$(vMinDate)';

            • Re: Prior Month
              Todd Johnston

              Hi Chris,

               

              Thanks for the timely response.  This absolutely works for creating the date, however it only seems to respond with the '>' symbol.  As I am trying to report on just the one specific month, I am trying to limit it to just that month.  However, by using the '=' alone, no records are being pulled.  Any ideas?

               

              Thanks