7 Replies Latest reply on Jul 10, 2018 8:03 AM by Jim Dehner

    Fiscal Week calculation

    Michael Niven

      My Fiscal Week ID values are as follow (for example):

      ....

      201849

      201850

      201851

      201852

      201901

      201902

      201903

       

      The following code is supposed to allow me to display 4 different metrics: Last Week Sales, Last 4 Week Sales, Last 13 Week Sales, and Year to Date Sales.

       

      The only issue I am having is with the Last 4 Week Sales and Last 13 Week Sales. I am uncertain how to write the code to make it where it pulls both 2019 and 2018 values. As it is now, it takes the Maximum week and value and subtracts by whatever the integer is, which will not pull the prior year. So in this case, Last 4 Weeks and Last 13 Weeks only pulls the 2019 values (when we want it to pull them to fit the metric identification, so for example, Last 4 Weeks should pull 201903, 201902, 201901, and 201852).

       

       

      CASE [Time Period]

       

          when 1 THEN

              IF [Max Week] = INT([Fiscal Week ID]) THEN

                      'True'

                  END

          when 2 THEN

              IF INT([Fiscal Week ID]) >= [Max Week] - 3

                  THEN

                      'True'

                  END

          when 3 THEN

              IF INT([Fiscal Week ID]) >= [Max Week] - 12

                  THEN

                      'True'

                  END

          when 4 then

              IF LEFT(STR([Fiscal Week ID]),4) = "2019"

                  THEN      

                      'True'

                  END

      END

       

       

       

      Thanks!