How to select date range values in Qlikview

[#1]

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 ............

[prieper]

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

[#3]

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?

[#4]

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 .

[Miguel_Angel_Baeyens · Accepted solution]

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.

[#6]

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

[#7]

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?

[#8]

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

[#9]

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

[blaise]

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.