4 Replies Latest reply on Mar 20, 2016 1:45 PM by Rody Zakovich

    53 weeks issue

    saurabh.c.0

      I have a purchase date field in database. When I use week(purchase date) in my report , I get 53 weeks on x axis. How to trend a measure for 52 weeks only. Kindly let me know what formula should I use to handle this year end issue.

        • 1. Re: 53 weeks issue
          Łukasz Majewski

          I do not quite understand why you think it is an issue since in Gregorian calendar a year has either 365 or 366 days which - depending on week start setting - yields 52 or 53.

          Nevertheless you may use this formula instead:

           

          DATEPART('week', [purchase_date])%52+1
          

           

          although the 53rd week will effectively become 1st. You could modify your [purchase date] field to backward those 53rd weeks to the 52nd:

           

          IF DATEPART('week',[purchase_date]) = 53 THEN DATEADD('week', -1, [purchase_date]) ELSE [purchase_date] END
          
          • 2. Re: 53 weeks issue
            Rody Zakovich

            The 53 Week "issue" comes from the how Dates are configured in Tableau.

             

            When you choose "Week Number" Datetrunc from the Dropdown, what Tableau is showing is the Week numbers according to the Default Week Start Day property.

             

            If you haven't changed the settings, Sunday, is the default Start week. So Tableau is essentially showing you Weeks, by the number of Sundays in the year.

             

            This behavior is expected, and follows most business logic. If you want to look at "Weeks" in terms of Monday - Sunday, you need to change the property accordingly. However this does not necessarily mean you will show 52 Weeks everytime. For the same reasons Łukasz Majewski described. Additionally, the Gregorian calendar doesn't break down to 52 Weeks exactly (When looking a 7 day intervals)

             

            But ff you want to look at Weeks in Terms of 7 day intervals starting from January 1st. Then you could use a formula like so

             

            FLOOR((DATEDIFF( 'day',  DATE( STR(YEAR([Purchase Date])) + '/01/01'), [Purchase Date] ) ) / 7) + 1

             

            This will allows result in "53 Weeks" but it is relative to a base Date of January 1st, and counts in 7 Day Increments.

             

            Just depends on what you are after.

             

            Regards,

            Rody

            • 3. Re: 53 weeks issue
              Łukasz Majewski

              hey Rody

              I would  say:

               

              INT(DATEDIFF( 'day',  DATETRUNC('year', [Purchase Date]), [Purchase Date] )  / 7) + 1
              

               

              no need to cast between string and date

              • 4. Re: 53 weeks issue
                Rody Zakovich

                Haha! Looks like I couldn't see the Forest for the Trees!

                 

                Don't know where my mind was at on the one lol

                 

                Thanks,

                Rody