1 Reply Latest reply on Dec 21, 2016 9:24 AM by Joe Oppelt

    Relative filter based on date parameter

    Edward Yeldham

      Hello,

       

      I have sales info showing year on year by month.  Currently the user selects how many years they want to see by selecting a relative filter of the number of previous years they want to see based on the transaction date of the data.

       

      I would like the user to be able to select a date and for the filter to work from this date selected.  Currently it works from todays date (I assume) by default.

       

      I have made some progress as I created a calc field which I called "Years to Show" as below

       

      IF  [Select Date] > [Transaction Date] THEN [Transaction Date] else [Select Date] END  - my aim being that the biggest date would be the selected date.

       

      I then used this on the filter shelf.

       

      I selected the filter to show me the previous 2 years.

       

      However assuming today is the 21st Dec 2016 when I chose my date as 30/6/2016 in the parameter control (as this is the last day of our fiscal year) the filter showed my data from Jan 2015 onward.  In effect only 18 months of data.

       

      It should show me 2 years of data back from the 30/6/2016 which would be 1/7/14 to 30/6/15 and 1/7/15 to 30/6/16.

       

      The filter is working (I assume) still somehow from todays date and going back from there.

       

      Help/guidance appreciated on how I achieve this!

       

      Regards

       

      Ed

        • 1. Re: Relative filter based on date parameter
          Joe Oppelt

          Taking a best guess based on what you described.

           

          If the user selects 2 for the number of years to see, and if the user selects "June 30, 2016" as the start date, make this calc:

           

           

          If [transaction date] <= [Start date parameter] and

          [transaction date] >= DATEADD('year', -[years to view parameter], [start date parameter]) then 1 else 0 END

           

          Put that calc on the filter shelf, and select for value = 1.  This will get you all rows with transaction date between June 30, 2014 and June 30, 2016.  (Notice that I put a negative sign in front of the middle argument in the DATEADD function.  This will add a negative value from the parameter.)