1 Reply Latest reply on Feb 8, 2019 1:33 PM by Ken Flerlage

    How to show the prior week's results when there is a filter in place

    mina cosentino

      Unfortunately, I cannot add the workbook, as it includes confidential data. But I've included a screenshot below to help clarify.

       

      Basically what you see is the names of the SEs and their performance against a certain goal (which is a parameter). The parameter includes four options that, when changed by the user, affect the 'Measure Selected' calculation. What I'm trying to do is have a line/shape to indicate how they performed the week prior in comparison to currently. The issue I'm running into is that when using the fiscal week as a filter (which is numbered 1-52, but the week starts on a Friday), I'm unable to 'grab back' that data for the previous week. Also, I have it included in a dashboard (also below), so taking away the filter isn't an option, unless it works with the other three sheets as well. Not sure if there is an easy way to achieve the prior week performance piece of this.

       

      EDIT: Also important to add... users should have a choice of which week they'd like to look at, so it would need to work dynamically with that.

       

      Screen Shot 2019-02-07 at 08.17.44.png    Screen Shot 2019-02-07 at 08.25.00.png

        • 1. Re: How to show the prior week's results when there is a filter in place
          Ken Flerlage

          You should consider using a parameter instead of a filter. A filter will actually remove all the data that doesn't match it, so that prior week information simply isn't available to report. But, you can use a parameter, populate it with your week numbers, then create some calculated fields to include the current and prior week.

           

          Here's an example using Superstore. Start by creating your parameter. I'll call it simply Week.

          Now we want to create a calculated field which will look at the actual dates in our data and match anything that is in the selected week or the piror week.

           

          Include

          // Include both the selected week (from the parameter) and the prior week.

          IF DATEPART('week',[Order Date])=[Week] THEN

              // Always include the selected week.

              "Include"

          ELSEIF DATEPART('week',DATEADD('week',1,[Order Date]))=[Week] THEN

              // Prior week. Include it.

              "Include"

          ELSE

              // Neither prior or current week, so exclude it.

              "Exclude"

          END

           

          Now drag Include to filters and set it to only include the value "Include".

           

          Finally, build your chart.

           

          Now when you select a week, the calculated field will filter out all data except that week and the prior week.

           

          See attached.