Filter on date range...

[#1]

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.

[nirav_bhimani]

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

[PrashantSangle]

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