3 Replies Latest reply on Jun 10, 2012 4:26 AM by Andrew Watson

    Week Level In Date Dimension

    John Stubblefield

      Hello,

      I'm using the standard date dimension with my date field.

      However, it does not have week level (standard is Year --> Quarter --> Month --> Day)

      Is there a sample tableau that includes how to build week level?

       

      Also, is there a way to filter on the current month (or week)?  The current month is not complete, so the chart shows big dip since I'm comparing YOY (see attached).

       

      Sincerely.

        • 1. Re: Week Level In Date Dimension
          Tracy Rodgers

          Hi John,

           

          There is currently not a way to add week into Tableau's built-in date hierarchies. However, a couple of calculated fields can be created from the date field and put into a manual hierarchy. The formulas would look similar to the following:

           

          datename('year', [Order Date])

          datename('quarter', [Order Date])

          datename('month', [Order Date])

          datename('week', [Order Date])

          datename('day', [Order Date])

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Week Level In Date Dimension
            John Stubblefield

            I tried DATEPART('week',Local_Date)

             

            How about my second question?

            Also, is there a way to filter on the current month (or week)?  The current month is not complete, so the chart shows big dip since I'm comparing YOY (see attached).

             

            Is there a a formula to exclude current month (when month is selected)?

             

            Thanks!

            • 3. Re: Week Level In Date Dimension
              Andrew Watson

              Hi John

               

              I wrote a post a few weeks ago regarding the year - week - day date hierarchy you asked about initially that could be useful to you: http://reports4u.co.uk/2012/05/31/date-hierarchies-tableau/

               

              With regards to filtering out the current month you need to create a calculated field along the lines of (apologies, I'm writing this without testing so please modify if required) IF MONTH(Local_Date) = MONTH(TODAY()) AND YEAR(Local_Date) = YEAR(TODAY()) THEN 'FALSE' ELSE 'TRUE' END

               

              Drag the calculated field to the filter shelf, set the value to true and it should remove the current month. It should be simple enough for you to modify this formula to remove the current week as well.

               

              Andrew