2 Replies Latest reply on Dec 2, 2016 7:51 AM by Jordan Ross

    Show Week Dates (Start and End) Instead of Week Number

    Jordan Ross

      Hi All,

       

      I feel like this should be pretty straight forward but I'm having a lot of difficulties with this. I have a basic chart displaying counts/rates over time, grouped by week.

       

      When you apply the week date level it gives it the week number (Week 1, week 2, etc.). I would like it to give the first and last date of that week, in MM/DD/YYYY format.

       

      I have found multiple threads discussing it, but they all point to this post: http://www.tableausoftware.com/support/knowledge-base/show-week-instead-week-number and multiple people had success with it. However, this support page is no longer active and the site it takes you to doesn't have what I'm looking for.

       

      Anyone know how to go about doing this?

        • 1. Re: Show Week Dates (Start and End) Instead of Week Number
          Santiago Sanchez

          Hi Jordan,

           

          Here's one way to do it. You can create a calculated field that looks like this:

           

          STR(DATE(DATETRUNC('week', [Order Date])))

          + " to " +

          STR(DATE(DATETRUNC('week', DATEADD('week', 1, [Order Date]))) -1)

           

           

          DATETRUNC returns the first day of the week (or any date part you specify within the function). That means, it alone can give us the beginning of the week. For the end of the week, we can use DATETRUNC and DATEADD to get the first day of next week and go back one day.

           

          Hope this helps!

          4 of 4 people found this helpful
          • 2. Re: Show Week Dates (Start and End) Instead of Week Number
            Jordan Ross

            Thank you! This gets me part way there!

             

            At first I though it would perfectly, but I realized that because it is making them into a string, it is now sorting my groupings alphabetically. I can get it to sort properly by putting both this calculated field and my date field by week in the column shelf. I just have to figure out if I can switch the header locations, or do a workaround in with same dashboard tricks.

             

            Two more questions:

             

            1) my dates are stored as datetime20 (from SAS), so they also have a time component. It is therefore displaying the dates with time, for example "Aug 21 2016 0:00:00 to Aug 27 2016 0:00:00." Do you know how to get rid of the time portion?

             

            2) Is there a way to set the date format? So instead of "Aug 21 2016" it could be "08/21/2016" ?

             

            I tried to solve both of these issues by adding STR(DATEPARSE('mm/dd/yyyy', ....)) to each line, but the DATEPARSE statement is resulting in an error "Oracle database error 1858: ORA-01858: a non-numeric character was found where a numeric was expected"

             

            I also tried breaking down the calculation into three separate calculated fields (one for each DATE function, and a third to combine then back together using STR) and setting the default date format to mm/dd/yyyy but it still displays as "Aug 21 2016 0:00:00".