3 Replies Latest reply on Jan 8, 2018 10:11 AM by Jim Dehner

    Last 3, 6, or 12 Months Filter

    Steven Cross

      Hopefully an easy one here and I apologize in advance if this has already been answered in another forum but I just couldn't quite put it all together.


      In the attached workbook I have two different Items A and B (could be apples and bananas), and every record has a Made On Date and a Pass entry 1 or 0 depending on whether or not it passed. I did a simple calculation to get the Pass %.


      I want a three choice only filter that only allows the user to select from the last '3 months', '6 months', or '12 months', but I want the 3 Months to actually be the last 90 days (from today), 6 Months is the last 180 days, and 12 Months is the last 365 days. Sounds like I need some sort of Parameter, Case, Filter combination. Please advise. Thank you.

        • 1. Re: Last 3, 6, or 12 Months Filter
          Vinnie Ahuja

          Hi Steven,


          You are on the right track with your notion about using parameters, calculated fields and filters.


          First create a filter, it sounds like you want to display in months but calculate in days.  If so, this should work for you:

          Next create a calculated field:

          DATEDIFF('day',[Made On Date],today()) <= [Months]


          Add this calculated field to the filters shelf and set to "TRUE".  You will see your data change as you change the values of the parameter.

          1 of 1 people found this helpful
          • 2. Re: Last 3, 6, or 12 Months Filter

            Please see attached workbook.

            I am assuming, it will be easy to understand. so I am not giving any explanation here.

            Let me know if any question OR I have missed something.

            1 of 1 people found this helpful
            • 3. Re: Last 3, 6, or 12 Months Filter
              Jim Dehner

              Hi Steve


              I think this will do what you want

              First create the parameter as shown


              Then the formula becomes  - I included a start date parameter that could be changed to using a filter if that is really needed and a formula based on window max of the date filter



              and it should return this -





              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.