topic Re: Filter on date range... in QlikView

Filter on date range...

Fri, 16 May 2014 23:58:33 GMT

Not sure if this can be done the way I intend on setting this up, but I have data in a table that includes EffectiveDate (YYYYMM format) and I need to create a date range, using a span of 5 years (BeginDate, EndDate) in order to only filter on EffectiveDate within the range of those 2 dates. The user will select a YearMonth combinations from a List Box or Table Box if need be(ie. 201306), and then calculate 5 years back (Addmonths(<Selected Date>, -60), and for it to filter all EffectiveDate dates in that 5 year span. So, if List Box field name would be BeginDate (let's say 201306) and 5 years back would be calculated as EndDate (or 200806), then it would dynamically filter the data in the table with EffectiveDate between BeginDate and EndDate.

Re: Filter on date range...

nirav_bhimani — Sat, 17 May 2014 06:51:31 GMT

HI,

First create a date field in the script like Makedate(Left(EffectiveDate ,4), Right(EffectiveDate,2),1) as CalDate,

Then Try below expression

=Sum({<CalDate={'>=$(=Yearstart(Max(CalDate), -4))<=$(=Max(CalDate))'}, CalMonth=,CalQuarter=,CalYear= >} Sales)

Hope this will resolve your doubt.

Regards,

Nirav Bhimani

Re: Filter on date range...

PrashantSangle — Sat, 17 May 2014 06:53:50 GMT

Hi,

first convert your datefield

using date function

Date(date#(datefield,'YYYYMM'),'DD-MM-YYYY') as newDateField,

then use newDateField in your set analysis

Like,

Sum({<endDate={">$(=addYears(newDateField,-5))<=$(=max(newDateField))"}>}sales)

Regards,