4 Replies Latest reply on Jul 22, 2018 3:15 AM by Ankit Bansal

    How to get date out of week and year using Tableau Prep

    Christian Kilthau

      Hi everyone,

       

      I do have a question which keeps me sleepless at night:

       

      The goal is to deliver a date, e.g. 01/01/2018 from a given week number and a given year. The day of the date should always be the firt day of the weeknumer.

      Has anyone a Idea?

       

      Thanks, Chris

        • 1. Re: How to get date out of week and year using Tableau Prep
          Simon Runc

          hi Christian,

           

          So if you created a calculated field, something like this...

           

          DATE(DATEADD('week',(([Year]-2017)*52)+[Week],#2017-01-01#))

           

          That should do the trick. Not that just through co-incidence the first week/day of 2017 fell on the 1st Jan. You can also adapt this "starting date" if your financial year doesn't start at the start of the calendar year. This is just the "base" date from which it works out how many weeks to add to this date for each Week/Year combination.

           

          Let me know if that doesn't make sense, or doesn't solve your problem

          • 2. Re: How to get date out of week and year using Tableau Prep
            Christian Kilthau

            Hi Simon,

             

            thanks for the prompt reply on this issue. I tested the formula you provide me and it works fine, thanks! The only thing is, that the calculated date is always the last day of that specific week. How can i adjust this to gibe back the first day of that week? One more question: Is there any chance to get Tableau Prep adding data to a output file instead of creating always a new one? I have to collect data frequently once a week and it would be great if I just can use my Tableau prep workflow to only add the latest data in the file.

             

            Cheers

            Christian

             

            • 3. Re: How to get date out of week and year using Tableau Prep
              Simon Runc

              Sorry my bad...the formula should have been

               

              DATE(DATEADD('week',(([Year]-2017)*52)+[Week],#2016-12-25#))

               

              Week 1 2017 would add one week to the #2017-01-01# I had used, not zero weeks!! So I've jogged the start back a week!

               

              If your weeks started on a Monday, for example DATE(DATEADD('week',(([Year]-2017)*52)+[Week],#2016-12-26#)) would now start give the date for the Monday, so fiddling with this starting date should let you create the dates as you want.

               

              With regards an incremental refresh, rather than a full refresh (as we can do in Tableau Desktop for tdes and hyper files). Unfortunately not yet (as far as I know). I imagine it will come in at some point (as Tableau Desktop can...and they seem to be gradually getting Prep upto the level of things we can do in Tableau Desktop).

              • 4. Re: How to get date out of week and year using Tableau Prep
                Ankit Bansal

                Alternatively you can use below formula:

                 

                DATEADD('week',[Week]-1,DATE("01/01/"+STR([Year])))

                 

                In the formula you can change the starting date instead of "01/01" as per your financial year.