2 Replies Latest reply on Mar 9, 2017 6:45 AM by Filipe Hemsworth

    Comparing Week to date values Year on Year

    Filipe Hemsworth

      Hi everyone,

       

      A little while ago I created a report which would summarize sales for all of our products. One of the features of this report is the ability to compare sales for the period to date, to the same period to date last year.

       

      I got the Date Comparison functions from another Tableau Vizer on the forums:

      Current vs Previous Period to Date Comparison

       

      Using his method I was successfully able to create Year to Date, Quarter to Date, and Month to date values.

       

      First I created a calculated field to calculate this year's period to date Sales to date using the following code:

      IF

          (DATETRUNC([Period], [Date]) =

          DATETRUNC([Period], [Date Selection])

          AND

          DATETRUNC('day', [Date]) <=

          DATETRUNC('day', [Date Selection]))

      THEN

          [Sales]

      END

      Then this for Last year period to date:

      IF

          DATETRUNC([Period], [Date]) =

          DATETRUNC([Period], DATEADD('year', -1,[Date Selection]))

          AND

          DATETRUNC('day', [Date]) <=

          DATETRUNC('day', DATEADD('year', -1,[Date Selection]))

      THEN

          [Sales]

      END

       

      The [Date Selection] field is a simple date parameter I created, and the [Period] is the parameter used to define the period to compare to, which contains the values 'day', 'week, 'month', 'quarter', and 'year'

       

      My question to you all is; how would I go about getting my week period working? Taking the example of 04/12/2016 which the last day of the week (starting Monday 28/11/2016). I want to compare this to the same Monday - Sunday last year. The formula I have used is indeed picking up the start of the week last year correctly (30/11/2016), but as this year's week ends on the 04/12/2016, it is also cutting off last year's week on the 04/12/2015 (instead of the 06/12/2015)

       

      Any ideas?

       

      Thanks in advance!

        • 1. Re: Comparing Week to date values Year on Year
          Olga Tsubiks

          Hi Filipe Hemsworth,

          I think these two pieces of information could be helpful to you:
          1. DATETRUNC has an optional [start_of_week] parameter. You can set it to whatever you want so that your function has the following format: DATETRUNC(date_part, date, [start_of_week]).

          2. If you did not specify the start of week, Tableau will use the data source to determine the start of week. You can learn more here: Date Properties

           

          I've created a blog post about time period comparison using a similar approach. You can find my solution, together with the workbook, there.

           

          Hope this helps!

          • 2. Re: Comparing Week to date values Year on Year
            Filipe Hemsworth

            In-case others have had this issue, here is the solution I ended up using. A bit of a rough work-around, but it should work for the next few years until the number of days between dates year on year change from 2.

             

            IF 

             

              DATETRUNC([Period], [Date]) =

                DATETRUNC([Period], DATEADD('year', -1,[Date Selection]))

             

            AND

             

                  DATETRUNC('day', [Date]) <=

             

                IIF(STR([Period])="week",

                DATETRUNC('day', DATEADD('year', -1,[Date Selection])+2),

                DATETRUNC('day', DATEADD('year', -1,[Date Selection])))

             

            THEN

             

              [Prod]

             

            END