4 Replies Latest reply on Apr 7, 2018 4:31 PM by Mike Mainzer

    Select one filter value (year) and include previous year

    L L

      I want to setup a quick filter that is a single selection that is a year value. When you select a year I want to include the previous year in the data. So is quick filter is set to 2018 the view will show 2018 and 2017 only. I cannot use parameters because the client wants this to be dynamic so he will not have to update the parameter every year to include the new year. How can I accomplish this?

        • 1. Re: Select one filter value (year) and include previous year
          Jim Dehner

          Good morning

          here are 2 ways - there are probable more

           

          you can use a relative date filter - place the date on the filter shelf and select relative date and follow the image below

           

           

          the second way is to create a filter based on the max date in the data set

           

           

           

           

           

          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.

          • 2. Re: Select one filter value (year) and include previous year
            L L

            Thank you for the response but neither of these will work because I want to be able to choose a year not just the current year and show the previous year, so I would have a single value year filter and say I selected 2015 it would show 2015 and 2014 and if I choose 2016 it should show 2016 and 2015. I could do this with parameters but the client wants this report to be dynamic and parameter don’t update automatically.

            • 3. Re: Select one filter value (year) and include previous year
              Jim Dehner

              That will limit what can be done -  there are several to do this

              where the date is filtered to the last selected date in the filter list BUT the preceding year also needs to selected -

              any or all the preceding dates can be selected but at least both of the years you want need to be selected - i.e. can't filter out the date then bring it back in

               

               

              Jim

              • 4. Re: Select one filter value (year) and include previous year
                Mike Mainzer

                You need to use a parameter in combination with a couple calculated fields.

                 

                First, create your parameter control

                1. Right-click parameter at the bottom and select 'Create a parameter...'

                2. Name the parameter 'Choose Year'

                3. Set the parameter to integer, select 'list', and enter the years you want to make available

                4. You'll see a menu for 'display format' - click it and then modify the number to have no decimals and no thousands separator

                5. Click OK, right-click your new parameter control and select 'Show Parameter Control'

                 

                Create calc fields

                Calc 1. Selected Year Value = IF [Enter your year field here] = [Choose Year] (the parameter control) THEN [Measure] (whatever data you're trying to show) END

                 

                Calc 2. Previous Year Value = IF [Enter your year field here] = [Choose Year] - 1 THEN [Measure] END

                 

                At this point, I'm not sure what you want to show, but let's say you wanted to show profit by region.

                 

                You would put Region on the rows.

                Then you would put your two calculated fields onto columns.

                Make sure you display your parameter control.

                Then just select your years via parameter control, not a filter.

                 

                Hopefully this works.