3 Replies Latest reply on Apr 18, 2016 4:26 AM by Hui Mien Lim

    Dynamic filtering displaying historical results

    Hui Mien Lim

      Hi all,

       

      I am trying to display past 12 months results base on a report month filter.

       

      For example: Report Month filter = Feb 2016, chart to display March 2015 to Feb 2016.

      and if Report Month filter = Jan 2016, chart to display Feb 2015 to Jan 2016 and so on...

       

      Able to advise how can i go about doing this?

       

      Thanks!

        • 1. Re: Dynamic filtering displaying historical results
          Esther Aller

          Hi Hui,

           

          Having the customer select one month and then the view will be filtered by the previous 12 months can be accomplished using a parameter to select the month and a calculated field to filter the view.

           

          An easy way to make a parameter that contains all possible months is to first create a calculated field that truncates the date to the month level and then create a parameter from that calculated field (my directions are using the sample data set Superstore):

           

          1. Create a calculated field with a name like "Month of Order Date" with a calculation similar to the following:

            DATETRUNC( 'month', [Order Date] )
          2. Right-click [Month of Order Date] in the data pane and select Create > Parameter...
          3. Modify the parameter as desired and click OK (I changed the Display format to show only month name and year)
          4. Right-click [Month of Order Date Parameter] in the data pane and select Show Parameter Control

           

          After creating the parameter we need to create a calculated field to filter the view:

           

          1. Create a calculated field with a name like "Last 12 Months Filter" with a calculation similar to the following:

            [Order Date] <= [Month of Order Date Parameter]
            AND
            [Order Date] > DATEADD( 'month', -11, [Month of Order Date Parameter] )

          2. Drag [Last 12 Months Filter] to the Filters shelf
          3. In the Filter dialog, check True and click OK

           

          I hope this answers your question!

          1 of 1 people found this helpful
          • 2. Re: Dynamic filtering displaying historical results
            Hui Mien Lim

            Hi Esther

             

            Thanks for the reply.

             

            I have another question pertaining to calculating historical year to date.

            • 3. Re: Dynamic filtering displaying historical results
              Hui Mien Lim

              Hi Esther

               

              Thanks for the reply.

               

              I have another question pertaining to calculating historical year to date.

               

              for example, Report month = March 2016. YTD should calculate from Jan 2016-March 2016.

               

              If report month = March2015, YTD should calculate from Jan 2015-March 2015. Able to advise how can this be done?

               

              Thanks