Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to select date range values in Qlikview

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

Tags (1)
1 Solution

Accepted Solutions

How to select date range values in Qlikview

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.

17 Replies
prieper
Honored Contributor II

How to select date range values in Qlikview

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

Not applicable

How to select date range values in Qlikview

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?

Not applicable

How to select date range values in Qlikview

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

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.

Not applicable

How to select date range values in Qlikview

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

Not applicable

How to select date range values in Qlikview

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?

Not applicable

How to select date range values in Qlikview

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

Not applicable

How to select date range values in Qlikview

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

blaise
Contributor III

How to select date range values in Qlikview

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.

Community Browser