1 Reply Latest reply on Aug 12, 2016 7:01 AM by Benjamin Greene

    Rolling 6 weeks

    Sonia Kaur

      Hi,

       

      I need some help with creating a rolling 6 week calculation.  Within my dimensions I have Year (Which contains 4 years, 2016, 2015, 2014 & 2013) and Weeks (1 to 52).

       

      I need to create a report that compares the rolling 6 weeks in current year to the same rolling 6 weeks in the previous year, and bring back sales, Orders and Profit.

       

      I have gone through the forum however, as I have a number of years within my dimensions I'm not sure how to make this work.

       

      Thank you in advance.

        • 1. Re: Rolling 6 weeks
          Benjamin Greene

          Try creating a new calculated field with the following syntax:

           

          IF (YEAR(TODAY())=[Year]

          OR YEAR(TODAY())-1=[Year])

          AND DATEPART('week', TODAY())-5<=[Weeks]

          AND [Weeks]<=DATEPART('week', TODAY())

          THEN "YES"

          ELSE "NO"

          END

           

          And then drag that field to the Filters card and filter on "YES"

          1 of 1 people found this helpful