Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Data filtering by date or date range

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

Re: Data filtering by date or date range

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

Re: Data filtering by date or date range

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

Re: Data filtering by date or date range

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';

Employee
Employee

Re: Data filtering by date or date range

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
New Contributor II

Re: Data filtering by date or date range

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' ;

 

 

 

Community Browser