1 Reply Latest reply on Jun 24, 2016 10:12 AM by Yuriy Fal

    Setting Custom week #'s

    Stephen Hart

      I have a report that we run weekly. The first week needs to include 12/30/2015-1/5/2016. I was trying to use something like INT(([Posting Date]-Date(2015-12-30))/7)+1 to have 12/30/2015 the starting date and assigning a week number based on the calculation. When putting this in as a calculated field I get a 4 digit number and the date range does not seem to be correct. Any thoughts?

       

      Thanks

        • 1. Re: Setting Custom week #'s
          Yuriy Fal

          Hi Stephen,

           

          You may want to change Week # (from 53 to 1) -- 

          only for those days belonging to Week 53.

          The calc could be like this (based on Sample Superstore):

           

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

          THEN DATEADD('day', DATEDIFF('day', [Order Date], DATEADD('year', 1, DATETRUNC('year', [Order Date]))), [Order Date])

          ELSE [Order Date]

          END

          If you take a Discrete Week aggregate from the above date, Week 53 becomes Week 1.

          Even if it fits your discrete week # scenario, other aggregates on this field would fail.

           

          Hope it could help anyway.

           

          Yours,

          Yuri