2 Replies Latest reply on Jul 30, 2018 7:45 PM by Helen Wang

    How to calculate rolling six weeks

    Helen Wang

      I have a quick question on how to calculate the newest rolling 6 weeks average “Sell Thru Quantity”. If you have some time this week may I drop by and have your opinions?

       

      I have the version to calculate the most up-to-date sells, I am wondering, do you have any advice on how I can edit it into the newest rolling 6 weeks?

       

      IF

      DATEPART('quarter',[Current Quarter]) = 1

      AND

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

      AND

      DATEPART('quarter',[Fiscal Quarter]) = 4

      AND

      [Fiscal Week# (Quarter)] <= [Current Week of Quarter]-1

      THEN

      [Sell Thru Dollars]

      ELSEIF

      DATEPART('quarter',[Current Quarter]) = DATEPART('quarter',[Fiscal Quarter])+1

      AND

      DATEPART('year',[Current Quarter]) = DATEPART('year',[Fiscal Quarter])

      AND

      [Fiscal Week# (Quarter)] <= [Current Week of Quarter]-1

      THEN

      [Sell Thru Dollars]

      ELSE

      NULL

      END

       

       

      Above is the code I have written in my company database. I cannot share my company database.

      I have attached a sample workbook, and please use that to instruct me on how to calculate 6 rolling weeks average. Thank you