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: 
Not applicable

Data filtering by date or date range

Hi,

I am new to QlikSense and have a question. I can connect to my data source and am able to retrieve the data. How do I select only a certain date range, say this year to date or even add a filter that can be updated to change the date range?

At the moment I have all data going back to 2009

6 Replies
Not applicable
Author

Add fields to your load, that will cover your filter requirements

e.g. YEAR(MyDate) AS MyYear,

     MONTH(MyDate) AS MyMonth

then create a "Filter Pane" on your report, adding MyYear & MyMonth (and anything else you like) as Dimensions

This will filter your data.

Sample load script:

LOAD

     Year(MyDate)     AS MyYear,

     Month(MyDate)     AS MyMonth,

*;

SQL

SELECT MyDate,

               Field1,

               Field2

FROM dbo.MySQLTable;

Not applicable
Author

Important note to add here is that you could create the objects in the Master items list - so you don't need to go back to the script.

Using The Master Library (video)

Not applicable
Author

Eager to know what the general preference is?

I have found that when using Master Items:

  • Don't need to amend the load script (as you point out)
  • Easily "tweaked"
  • Unfortunately display the function when used as a filter (so rather than showing "MyHour=1" it shows "Hour(MyTime)=1"

Putting the calculations in the load script:

  • Requires forward planning
  • Needs the data reloading after any "tweaks"
  • Displays in a more user friendly format when filtered

Guessing there are differences in the reload execution times, as well as RAM usage - is it a "depends on how much data you are loading and how often" preference?

Not applicable
Author

It depends on how your data source is and what you invite . Do you want to keep the application small You have already narrow down the script . Here is an example of a QVD .

Beispiel:
LOAD
Daten1,
Daten2,
Datum
FROM

(
qvd) where year(Datum) > '2010';

Michael_Tarallo
Employee
Employee

Hello Robert,

Take a look at this video here:

Understanding the Master Calendar (video)

Available here:

New to Qlik Sense Videos

At the 9:46 mark I explain how to use the list box with the search filters to get date ranges:

Understanding the Master Calendar - Qlik Sense and QlikView - YouTube

Check this out in the help too:

http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Search/data-search.htm?Highli...

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Regards,
Mike Tarallo
Qlik
rsekhar86
Contributor II
Contributor II

Sorry for the timing of my question, when I apply date range filter like below, Its not working. My date format  is YYYY-MM-DD - 00:00:00, and I have converted that as stated below. 

Sales_History:

Load 

Record_ID,
Material,
Material_Desc,

Date(Create_Dt, 'MM-DD-YYYY') as Create_Dt

Where Create_Dt between '2018-10-01' and '2018-10-31' ;