topic Re: Re: Date Range Selection in QlikView

Date Range Selection

Thu, 06 Sep 2012 14:17:25 GMT

My requirement is I have date column and my user needs wants to select the data by date ranges. Please let me know how can I achieve this. Currently I can only select only single date.

Re: Date Range Selection

Thu, 06 Sep 2012 14:21:49 GMT

I recommend using a Master Calendar in your script. See 'Dates' tab in the attached example script.

Re: Date Range Selection

Miguel_Angel_Baeyens — Thu, 06 Sep 2012 14:22:20 GMT

Hi,

You can take this QlikView app as an example using two calendar / slider objects using variables and how to use these variables in the chart expressions.

Hope that helps.

Miguel

Re: Date Range Selection

Jason_Michaelides — Thu, 06 Sep 2012 14:48:43 GMT

This code (written by Rob Wunderlich) is absolutely invaluable for creating common date ranges. Users love it! I think a fuller example may be in his QV Cookbook (http://robwunderlich.com/downloads/)

/* 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;

Re: Date Range Selection

techvarun — Mon, 02 Sep 2013 11:29:03 GMT

It is showing page not found

Re: Re: Date Range Selection

Mon, 02 Sep 2013 11:43:00 GMT

Hey,

no idea what was the old document but here is what i used as example for my applications with data ranges.

Re: Re: Date Range Selection

techvarun — Mon, 02 Sep 2013 11:46:30 GMT

Thanks for the quick reply Juan.

Regards

Re: Date Range Selection

Thu, 10 Apr 2014 10:07:29 GMT

Hey. I hope this document will help you figure it out. it's very simple.

Re: Date Range Selection

Thu, 10 Apr 2014 10:25:36 GMT

Check the attached