Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Filter on date range...

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.

2 Replies
Highlighted
Partner
Partner

Re: Filter on date range...

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

Highlighted

Re: Filter on date range...

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,

PS

Great dreamer's dreams never fulfilled, they are always transcended.