topic Re: How to select a range of dates from list box in QlikView

How to select a range of dates from list box

Tue, 07 Jan 2014 14:57:22 GMT

Hi,

I have list box DATE. The dates are from 2006 to 2013. My user wnats to select the dates from 01/01/2007 to 31/12/2007 all at once by clicking CTRL+selections in list box with out dragging.

Is there any possibility in qlikview to get dates using BETWEEN and AND

for eg: date BETWEEN 01/01/2007 and 20/05/2007

please could anyone help me in this.

Thanks.

Re: How to select a range of dates from list box

MK_QSL — Tue, 07 Jan 2014 15:07:59 GMT

Dont have idea using BETWEEN and AND but I have below solution which I frequently using for our clients.

Create two new variables…

vStartDate and vEndDate

Now go to Settings – Document Properties – Triggers

Variable Event Triggers

Select vStartDate

OnChange Add Action

Add

Select in Field

Field = Your Date Field

Search String = ='>='&vStartDate & '<=' & vEndDate

Do the same for vEndDate

Now insert Input Box and add these two variables

Go to number tab and make sure that the formatting of vStartDate and vEndDate is same as your Date Field….

Hope this help

Re: How to select a range of dates from list box

sebastiandperei — Tue, 07 Jan 2014 15:41:47 GMT

When you click on the title of the list box, just type:

>=1/1/07 <=31/12/07

Re: How to select a range of dates from list box

MK_QSL — Tue, 07 Jan 2014 15:51:27 GMT

But using this method we still have to select dates by dragging..

Re: How to select a range of dates from list box

Tue, 07 Jan 2014 16:29:20 GMT

Hi,

I am happy with your suggestion in this scenario.But i have another doubt that is

that

if user selects the data like invalid range suppose start date is greater than end date.

i need show error msg like select valid range this thing.

how can we solve this issue.

regards

Mahesh Thalluri

Re: How to select a range of dates from list box

MK_QSL — Tue, 07 Jan 2014 16:35:57 GMT

We can use condition...

vFromDate <= vStartDate

For List Box Under Layout Tab....

and For Straight Table or Pivot Table...

Under General tab Calculation Condition...

Re: How to select a range of dates from list box

MK_QSL — Tue, 07 Jan 2014 16:38:09 GMT

Also, we can display a message in text box as

=if(vFromDate > vToDate, 'Invalid Selection')

with Layout Condition...

vFromDate > vToDate

Re: How to select a range of dates from list box

rwunderlich — Tue, 07 Jan 2014 16:48:33 GMT

Here's a variation on the FromDate/ToDate variables that validates the input dates.

http://qlikviewcookbook.com/recipes/download-info/enter-date-from-to/

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: How to select a range of dates from list box

Tue, 07 Jan 2014 16:49:54 GMT

Thanks it's working

Re: How to select a range of dates from list box

ngulliver — Tue, 07 Jan 2014 16:55:05 GMT

Hi, Amelia.

Have you considered using a slider ? If you set it to Multimode you can enable the users to select a range of dates and everything in between.

Regards,

Neil

Re: How to select a range of dates from list box

Tue, 07 Jan 2014 17:04:54 GMT

How can I use slider?please tell me

Re: How to select a range of dates from list box

MK_QSL — Tue, 07 Jan 2014 17:11:10 GMT

RIght Click

New Sheet Object

Slide/Calender Object

Input Style = Slider

Field = Your Date Field

Mode = Multi Value

Presentation - Scale need to change according to your need

But here also, user have to use slider from one date to another and it is not convenient if you have many dates as it is impossible to show all dates and also will consume more space in your sheet.

Re: How to select a range of dates from list box

MK_QSL — Tue, 07 Jan 2014 18:46:03 GMT

Hi Rob,

Can you explain how the check constraint is appearing...

Re: How to select a range of dates from list box

MK_QSL — Tue, 07 Jan 2014 18:48:42 GMT

got it.... !!! thanks for this example...

Re: How to select a range of dates from list box

stevelord — Wed, 08 Oct 2014 19:57:23 GMT

If you want between two dates, you can do it with one variable if one date is a fixed increment from the other. If the number of days can vary, the two variable approach is needed.

One of our tasks involves weekly challenges and the increment is always 7 days for those. So we'd use something like:

Count(Distinct if(Date>=vDate AND Date<vDate+7, UserID, 0))

Also a nice ham-fisted approach to selecting all dates there are is to make another field in script year(Date) as Year, setup a listbox for that, and click on the couple of years. Or some concats of year(Date)&' '&month(Date) as YearMonth in a listbox to handle date ranges that go from part of one year to part of the next. (I have people pushing back on having to type anything, and pushing back on having to click on more than two things... )

Re: How to select a range of dates from list box

Anonymous — Sat, 03 Oct 2015 06:12:17 GMT

Just press enter [return] and it will be selected