QlikSense : Filter a table based on StartDate and EndDate

[rajasekhar6067]

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.

[petter · Accepted solution]

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 )