topic How to select date range values in Qlikview in QlikView

How to select date range values in Qlikview

Mon, 09 Nov 2009 20:04:54 GMT

Hi all,

I want to select values in a date range

like all the values which are greater than 2009-08-15 and less than 2009-09-31?

i have taken two calender objects one for to select start date and onther for to select end date

now i want all the values between these given two dates should be selected.

Please suggest ............

How to select date range values in Qlikview

prieper — Mon, 09 Nov 2009 20:16:11 GMT

You need to assign each calendar-objects with a (to be created) variable.
Then you are able to refer to these variable in all formulas etc:
SUM(IF(Date > MyVariableFrom AND Date < MyVariableTo), Costs)

HTH
Peter

How to select date range values in Qlikview

Mon, 09 Nov 2009 20:20:58 GMT

Why two calendar objects?

You need select all values between two dates. Then you need drop three list object, with Year, Month and Day, to make easy make selections, then select the range you need.

Are there some peculiarity in your aplication to justify to calendar objects?

How to select date range values in Qlikview

Mon, 09 Nov 2009 21:50:25 GMT

Thanx for your reply,

I have done with list boxes as you suggested , but as data is huge and expressions are large ,

The proccessing time is more and CPU utilisation becomes more because of that application get stuck so i want to reduce data for fast processing

is it possible please suggest for any macro is there in Qlikview to do that .

How to select date range values in Qlikview

Miguel_Angel_Baeyens — Mon, 09 Nov 2009 22:07:24 GMT

Hello Rahul,

Why don't use a Master Calendar as suggested in the Developer Course? Take a look at the example to see if that can suits you.

//************** Quarters mapping Load **************
Quarters_Map:
MAPPING LOAD
 rowno() as Month,
 'T' & Ceil(rowno()/3) as Quarter
Autogenerate(12);

LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));
LET varMaxDate = Num(Peek('OrderDate', -1, 'Orders'));
LET vToday = Num(today());

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
 $(varMinDate)+IterNo()-1 AS Num,
 Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS OrderDate,
 week(TempDate) AS Week,
 year(TempDate) AS Year,
 month(TempDate) AS Month,
 day(TempDate) AS Day,
 weekday(TempDate) AS WeekDay,
 applymap('Quarters_Map', num(month(TempDate)), null()) AS Quarter,
 date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
 week(TempDate)&'-'&Year(TempDate) AS WeekYear,
 Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
 Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate Asc;

DROP TABLE TempCalendar;

Now with three listboxes you have all relevant dates to search.

P.S.: For copyright purposes, this code is not mine, it is used in the QlikView Developer Manual and exercises.

How to select date range values in Qlikview

Tue, 10 Nov 2009 00:59:37 GMT

Thank you very Much.............................

How to select date range values in Qlikview

Mon, 21 Dec 2009 21:52:07 GMT

Sorry for digging up this problem again but...

This all works nice if i wanted to select all values between year 2000 and year 2009. But I would have trouble to select a timeframe from '12.12.2009 to 18.01.2010' right?

How to select date range values in Qlikview

Tue, 02 Mar 2010 12:23:46 GMT

not sure if you have solved this or not, but I saw a demo application that had a slider used to select a date range.

I sometimes have to select a range of months (e.g.rolling 12 months), so have created a new concatonated field by appending the month to the year,

I found this can be used in a list box to select a range. With you, you are choosing specific days too, so have a look at the demo apps, you may get some inspiration. I cant remember which one it was though..

Regards,

Matt

How to select date range values in Qlikview

Tue, 09 Mar 2010 16:48:47 GMT

I can't see how the master calendar would allow you to filter all records with dates before a selected date.

How to select date range values in Qlikview

blaise — Tue, 09 Mar 2010 21:36:16 GMT

I solved this with use of a macro. First i created a DateRange table with only one field, DateRange (this one i autogenerated out of max(Date) and min(Date) from the actual date field.

I when created to variables, vDateStart and vDateStop. Two calendar objects was created, one for each variable with min(DateRange) and max(DateRange) as limits. A button triggers a macro that does a


obj.Select ">="& ActiveDocument.Evaluate("date(vDateStart,'YYYY-MM-DD')") &" <=" & ActiveDocument.Evaluate("date(vDateStop,'YYYY-MM-DD')")

You can of course skip the dateRange part and just let the user input the two dates into a input box, but the Calendar object are quite elegant and easy to use (with an inputbox the user can always input the date in a wrong format.

How to select date range values in Qlikview

Tue, 16 Mar 2010 16:14:53 GMT

thanks blaise, that has helped a lot.

How to select date range values in Qlikview

rwunderlich — Wed, 17 Mar 2010 06:56:01 GMT

Here's a version 9 macroless (uses Actions) solution that uses Input Boxes. It validates the input to ensure format and in the correct range.

I have a version 8 version that uses macros if you are on V8. (Search the Forum for EnerDateFromTo.qvw)

-Rob

How to select date range values in Qlikview

Anonymous — Wed, 17 Mar 2010 16:41:48 GMT

Rob, your solution here is excellent.

QlikView gave me something similar but it was way more primitive and unfinished and used macros.

I have implemented your solution on one of our documents and it works a treat. Will be rolling it out to our other documents, I'll give you a credit in the code.

Thanks

How to select date range values in Qlikview

rfarrenkopf — Wed, 17 Mar 2010 17:33:08 GMT

Rob,

I am unable to find you V8 version (EnerDateFromTo.qvw) by searching the forum. Could you point me to the correct location?

Thank you.

Bob

How to select date range values in Qlikview

rwunderlich — Wed, 17 Mar 2010 23:37:33 GMT


rfarrenkopf wrote:unable to find you V8 version (EnerDateFromTo.qvw) by searching the forum. Could you point me to the correct location?

You'll find the version 8 sample in this thread:
http://community.qlik.com/forums/p/17555/93336.aspx

BTW, a better way to search the Forum is Google with a site: filter. I went to Google and entered the search argument:

EnterDateFromTo.qvw site:qlik.com

-Rob

How to select date range values in Qlikview

Fri, 19 Mar 2010 17:28:01 GMT

Thanks Rob - your methodology has been invaluable!

How to select date range values in Qlikview

Wed, 13 Oct 2010 23:20:54 GMT

Rob,

I implemented the input boxes and triggers on my application following the code that you had in your solution but I have a problem. Is there a way to clear the input boxes using the clear button that is in the QlikView toolbar? When I click Clear, it clears everything except the input boxes and the date list associated with them.

Thanks,

Ken

How to select date range values in Qlikview

rwunderlich — Tue, 09 Nov 2010 01:34:31 GMT


Ken wrote: Is there a way to clear the input boxes using the clear button that is in the QlikView toolbar?

You could remove the OnChange action from the Date field if you want Clear to "Stick". To clear the input fields, you would need a some hidden field you could detect clear (OnChange) on and set the variable values.

-Rob