1 Reply Latest reply on Jul 26, 2018 9:01 AM by Joe Oppelt

    Convert Week (string) to WeekNumber (Date)

    Leon Mclean

      Hello all,

       

      Given my data set i am unable to get Date, but only [Week], [Month], [Year].

       

      These are all whole numbers E.g. [Week] = 21 // [Month] = 5 // [Year] = 2018

       

      As weeknumber is not recognized when i try to convert to a Date, is there a calculated field to incorporate all [Year] [Month] [Week] into one field and to be recognized as a Date dimension?

       

      The reason i am asking this, i am looking to create a calculated field that ultimately pulls the Latest Week and assigns revenue to that date (e.g. { FIXED [Last Week]: SUM([Revenue])}

       

      Many thanks,

      Leon

        • 1. Re: Convert Week (string) to WeekNumber (Date)
          Joe Oppelt

          I hate weekly reporting. 

           

          Yup, we can't build a straightforward date based on week values.

           

          But consider this.  Find your starting date for the first week of your corporate calendar.  For instance, this calc:

           

          DATE(Datetrunc('week',#1/1/2018#))

           

           

          I just plugged in a hard-coded date of January first.  Datetrunc gives me the first day of the week that the supplied date falls in.  The result is 12/31/2017.  Assuming that's what you consider the beginning of the first week of your year, then you can do this:

           

          DATEADD('week',[your week value], [calculation 1])