1 Reply Latest reply: Aug 16, 2018 4:54 PM by Petter Skjolden RSS

    QlikSense : Filter a table based on StartDate and EndDate

    Rajasekhar .

      Hi,

      I have a table of data which contains  sales information for an year. I need to filter the data in the table based on startDate and EndDate. Meaning, when a user select StartDate and EndDate, the table should display the data in between those dates.

       

      To achieve this, I have created date dimension and created two filters, StartDate and EndDate

      when i tried to filter the date column using the above two dates, it is failing but when i tried to filter the column by giving static values its working fine.

      Ex: Variables vFromDate=max(FromDate); vToDate=max(ToDate)

      Expression in Date column in the table =if(BusinessDate > '07/10/2018' and BusinessDate<'07/20/2018', BusinessDate) - Works Perfect. (uncheck include NULL values)

      Expression in Date column in the table =if(BusinessDate> $(vFromDate) and BusinessDate < $(vToDate),BusinessDate) - Error


      Appreciate your help.

        • Re: QlikSense : Filter a table based on StartDate and EndDate
          Petter Skjolden

          When you use Max or Min on a date Qlik will remove the text format of the date and just keep the numeric part. So be sure to wrap the Max and Min in a Date()-function to reapply the correct time format:

           

          vFromDate = Date(Max(FromDate));

          vToDate = Date(Max(ToDate));

           

          The expression should then be:

           

          =If( BusinessDate > vFromDate AND BusinessDate < vToDate,BusinessDate )

           

          or

           

          =If( BusinessDate > '$(vFromDate)' AND BusinessDate < '$(vToDate)' ,BusinessDate )