Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

toby_booth
New Contributor

How to set date range in filter pane?

Hello everyone,

My first post. I'm very new to Qliksense and have found a script (see below) that suits my purposes very well. However I am having difficulty understanding how to use my own data in it, instead of the auto-generated data the script provides as an example. Can anyone help?

For context, I got the script from this thread, https://community.qlikview.com/message/256243#256243

Thanks.

---

/* This section just creates some test data */

data:

LET vEndDate = num(today(1)); // End at today's date

LET vStartDate = num(addmonths(today(1),-14)) -1; // Start at 14 months earlier than today

TestData:

LOAD date($(vStartDate) + IterNo()) as Date

AUTOGENERATE 1

WHILE $(vStartDate) + IterNo() <= $(vEndDate)

;

/* End of test data */

/* This script demonstrates assigning Dates to named groups, such as "Yesterday" or "Last Week".

Date table was created on the previous tab.

*/

// Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundry.

LET vToday=today(1);

Ranges:

// Use the "evaluate()" function to execute the input function text and convert it to actual date values.

LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd

;

/* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.

The Start/End values are defined using standard QV date functions.

Semicolon is used for a field delimeter because some of the functions contain commas.

*/

LOAD * INLINE [

Range; RangeStart; RangeEnd

Today; vToday; DayEnd(vToday)

Yesterday; vToday-1; DayEnd(vToday-1)

This Week; WeekStart(vToday); WeekEnd(vToday)

Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)

Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1))

This Month; MonthStart(vToday); MonthEnd(vToday)

Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)

Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)

This Year; YearStart(vToday); YearEnd(vToday)

Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)

] (delimiter is ';')

;

/*

Use IntervalMatch to link the Date field into multiple Range fields.

Doing a JOIN avoids creating a messy synthetic key.

*/

JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges;

// Because we did a JOIN, we may drop the the Start/End fields.

DROP FIELDS RangeStart, RangeEnd;

3 Replies
isingh30
Contributor III

Re: How to set date range in filter pane?

Please share your data or app.

Thank you!

rittermd
Honored Contributor

Re: How to set date range in filter pane?

What are you trying to do?

The above script will run and creates Inline table with two fields (Date and Range)

If you create a new app and then copy this script into the Script Editor and then run that is what you will end up with.

You can then just create a simple table on a sheet and load the two fields to see what you got.

Re: How to set date range in filter pane?

Never seen that script before, it looks quite good.

It creates 2 tables called Ranges and TestData, which are joined via the common field Date.


Assuming your data has a date field then load your data renaming the date field to Date and it should auto join in.  Then you can create a  Filter Pane against the Range field, create a table with your data in it and select a Range value and see what happens.

For tidiness you could drop the table called TestData.