3 Replies Latest reply on Sep 12, 2016 8:10 AM by David Li

    Filter without affecting results AND keep previous year comparison

    Leslie Raillon

      Hi,

       

      Enclosed I have a graph of weekly YTD + weekly results.

      I have added a filter to show only the past 8 weeks...

       

      But when I do that previous year disappears...

       

      Any help?

       

      Thanks a lot

       

      Leslie

        • 1. Re: Filter without affecting results AND keep previous year comparison
          David Li

          Bonjour Leslie ! How does this look?

          I used a few steps to get here:

          1. I created a LOD calc that gets the last week for all years ([Max Date]).
          2. I used that field to create a filter ([Filter de date]) that cuts out weeks past the current week of the current year.
          3. I used a table calculation that keeps only the last 8 weeks ([Huit dernieres semaines]) and put that as a filter.
          • 2. Re: Filter without affecting results AND keep previous year comparison
            Leslie Raillon

            Hi David,

             

            Thanks a lot.

             

            I added the variables you created and it worked great!

             

            ... I tried to understand them but it is still a bit difficult for me though...for the moment

             

            Leslie

            • 3. Re: Filter without affecting results AND keep previous year comparison
              David Li

              Hi Leslie, let me try to explain: the main complication here is that there's usually a different number of weeks in the current year than in the previous years, because we're only partway through the current year. If we ignore that, then we could just do step #3, which just keeps the most recent 8 weeks in the data set. That's a table calculation that looks at every week and figures out how far it is from the very last week. If it's less than 8 weeks from the end, then it keeps it.

               

              Since we have different numbers of weeks in each year, in step #1, we use a LOD calculation to figure out the maximum week number in each year. We then truncate (cut off) the weeks after the minimum out of all of these. For instance, if we're in week 35 this year, then this calculation helps us cut off all weeks after #35 for the previous years.

               

              Combining these two, we get our solution.