5 Replies Latest reply on Dec 17, 2012 7:02 PM by Alex Kerin

    Calculate Year / Week / Day of Week

    brian.comeau

      Hi Tableau Community,

       

      While I'm getting used to Tableau, I'm struggling with how to wrap my mind around the calculation methodology for certain areas. In this particular case, I'm having trouble understanding how dates will be calculated to affect my visualization.

       

      The Situation:

      I have a set of Sales data going back for more than a couple of years and I would like to compare against history.

       

      1. Compare Today (Week 'n', Day 'x') to Last Year (Week 'n', Day 'x')
      2. Compare Last Year (Week 'n', Day 'x') to the Prior Year (Week 'n', Day 'x') if it exists, et al.
      3. Be able to filter this comparison by a category (e.g. Books Sales, Table Sales, etc.) or at an aggregate (e.g. Total Sales)

       

      The Approach:

      I've come up with the following so far, but now I've become lost.

       

      IF DATEPART('week',DATEADD('year',-1,TODAY())) = DATEPART('week',TODAY()) THEN

          IF DATEPART('weekday',DATEADD('year',-1,TODAY())) = DATEPART('weekday', TODAY()) THEN

              dblTC

          END

      END

       

      I haven't attached a sample tbwx file as the SuperStore data alone should be sufficient to provide a case study for this one.

       

      Thanks,

      Brian

        • 1. Re: Calculate Year / Week / Day of Week
          Alex Kerin

          The difficulty of the using week is that the start changes year by year. I would presume that you want to compare a Saturday to a Saturday?

          • 2. Re: Calculate Year / Week / Day of Week
            brian.comeau

            Hi Alex Kerin,

             

            That would be the idea. I assumed that the 'Week' function starts each year on the same day of the year and counts up 7 days. So Week 41 would always have Day 1 on a Sunday and Day 7 on a Saturday. If I then compare Week 41 in 2012 to Week 41 in 2011, both Day 1's would be Sundays.

             

            If that is not correct, then I will need another approach, as indeed the idea is to compare Day to Day  over years given that variances in Sales can occur on weekends and the like.

             

            Thanks,

            Brian

            • 3. Re: Calculate Year / Week / Day of Week
              Alex Kerin

              No, Week 1 starts on the 1st of Jan, so week 1 may only have one day in it (Saturday). For example, building on your equation (replacing today() with a parameter and [Sales] with the date so we can test):

               

              if datepart('weekday',[Order Date])=datepart('weekday',[Order Date Parameter])

              and datepart('week',[Order Date])=datepart('week',[Order Date Parameter])

              and datediff('year',[Order Date],[Order Date Parameter])=1 then [Order Date] end

               

              looks like it works until you choose the 3rd Jan of 2012, which is a Tuesday. Week 1 of 2011 only has a Saturday, no Tuesday, so the calculation returns null.

               

              You want to compare the first Saturday of the year to the first Saturday of last year. That's more complex as we have to create our own week definition. Thinking about that....

              • 4. Re: Calculate Year / Week / Day of Week
                brian.comeau

                Thinking about it algorithmically, we want to do:

                 

                1. MIN(DATEPART('day', [dtDate]) WHERE day of the week = 'Sunday') As Week 1, Day 1 As START

                2. WEEK/DAY = TODAY() - (TODAY() - START %7)

                 

                At least I think that makes sense:

                 

                Find the first Sunday of the year and that counts as 1/1. Take today and minus the date of 1/1, divide by 7 to find the week number (rounded down) and modular 7 to find the day of the week...

                • 5. Re: Calculate Year / Week / Day of Week
                  Alex Kerin

                  Yes - this is very similar to this issue: http://community.tableau.com/thread/121143

                   

                  But I never worked out the mod version.  I am now travelling for the rest of the week, so hopefully this will be at least a start for you.