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

# Last 3, 6, or 12 Months Filter

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

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

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 -

Jim

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.