Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rajasekhar6067
Contributor II
Contributor II

QlikSense : Filter a table based on StartDate and EndDate

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.

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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 )

View solution in original post

1 Reply
petter
Partner - Champion III
Partner - Champion III

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 )