8 Replies Latest reply on Jan 19, 2016 4:39 PM by rahul.patel.2

    Year over Year Like for Like Comparison by Week

    Tony Gruebner

      Hi,

       

      I am trying to set up a workbook that tracks the past 10 weeks and gives a YoY comparison for the same week of last year. I am having a few problems such as:

       

      • If I use week number it is difficult to filter out the irrelevant data (i.e. the data between the period of the 10 weeks last year the the 10 previous weeks
      • The graphs show week number but it would be better if they showed the start date of the week
      • I want the filter to automatically pick up the last 10 weeks so that I don't need to change every view at the start of each week
      • When it comes to the end of the calendar year dates become awkward

       

      Any suggestions.

       

      I have attached a workbook for some dummy data to work with if it helps.

       

      Cheers

      T

        • 1. Re: Year over Year Like for Like Comparison by Week
          Shawn Wallwork

          Tony, you can display start of week instead on week numbers (see attached). Be aware that in Tableau weeks always start on Sunday. (There's an idea out there to change this, find it and vote if you want). It's late, so can't answer your other questions, but this will pop it to the top of the Q so I'll see it in the morning.

           

          --Shawn

          • 2. Re: Year over Year Like for Like Comparison by Week
            Tony Gruebner

            Thanks Shawn

             

            We have also had a play with it and have come up with the following which works:

             

            To create a rolling LfL YoY comparison broken out by week over the past X weeks, you need to utilise a set of calculated fields and steps with the usage of parameters.

            First step is to set up a parameter as a date that you want to look backwards from. When this has been created, the parameter will be inserted into the calculated fields below. In the example we have on hand, we’re looking back a rolling date of 70 days or 10 weeks from a given date.

             

            First Calculated Field (Segmenting out the YoY rolling weeks):

            if ((datediff('day',[Parameter Date],[Sale Date])>=-70) and (datediff('day',[Parameter Date],[Sale Date])<0))

            then round((datediff('day',[Parameter Date],[Sale Date])/7)-0.49,0)

            elseif ((datediff('day',(dateadd('day',-364,[Parameter Date])),[Sale Date])>=-70) and (datediff('day',(dateadd('day',-364,[Parameter Date])),[ Sale Date])<0))

            then round((datediff('day',(dateadd('day',-364,[Parameter Date])),[ Sale Date])/7)-0.49,0)

            else NULL

            end

            This pulls out the data for the past 10 weeks from Parameter Date, and the past 10 weeks of the previous year (segmented as -1, -2, -3 etc indicating -1 week from Parameter date, -2 weeks, and so on). This field goes into the filters section, with NULLs filtered out.

            The parameter date will also allow you to choose where you wish a week to end and commence. Please note that the formula accounts for dates less than the Parameter date, so if you are choosing a Monday as the parameter date. It will take all date values from the previous Monday, up till the Sunday before the Parameter date.

            Please note that to accommodate for a leap year, the occurrences of -364 in the above formula needs to be changed to -365. Im sure you could right a calculation which sorts this out.

             

            Second Calculated Field (segmenting current year and past year):

            if ((datediff('day',[Parameter Date],[Sale Date])>=-70) and (datediff('day',[Parameter Date],[Sale Date])<0))

            then “Current Year”

            elseif ((datediff('day',(dateadd('day',-364,[Parameter Date])),[Sale Date])>=-70) and (datediff('day',(dateadd('day',-364,[Parameter Date])),[ Sale Date])<0))

            then “Previous Year”

            else NULL

            end

            From this, you can split out (we used colour) the past year Sales vs this year sales.

            Once this is done, we need to create another formula to ensure that the dates are consistent with what you would want to see.

             

            Third Calculated Field (Defining the dates):

            [Parameter Date]+([First Calculated Field]*7)

            This formula pulls the date.

            When being used along an axis, be sure to pick Week Number/Date Value in the options, and edit axis option to choose what dates appear along the axis.

             

            YoY Calculation:

            You can create another calculated field here or a calculation on the field that contains your sales volumes. The YoY will simply be the ‘% Difference from’ along the classifications you have created in the second formula (current year, previous year).

            • 3. Re: Year over Year Like for Like Comparison by Week
              sandeep kushwaha

              thanks tony

               

              its very useful.

               

              First Calculated Field (Segmenting out the YoY rolling weeks):

              if ((datediff('day',[Parameter Date],[Sale Date])>=-70) and (datediff('day',[Parameter Date],[Sale Date])<0))

              then round((datediff('day',[Parameter Date],[Sale Date])/7)-0.49,0)

              elseif ((datediff('day',(dateadd('day',-364,[Parameter Date])),[Sale Date])>=-70) and (datediff('day',(dateadd('day',-364,[Parameter Date])),[ Sale Date])<0))

              then round((datediff('day',(dateadd('day',-364,[Parameter Date])),[ Sale Date])/7)-0.49,0)

              else NULL

              end

               

              but in above formula can we replace [Parameter Date] with Max[Sale Date]  ?

              • 4. Re: Year over Year Like for Like Comparison by Week
                rahul.patel.2

                Tony I am new to Tableau can you please tell me why have you used the following as i have doubt.

                1] 0.49 in calculation (First Calculated Field (Segmenting out the YoY rolling weeks))

                  

                2]And now that we are in 2016 will this 0.49 change?

                 

                2]Third Calculated Field (Defining the dates):

                [Parameter Date]+([First Calculated Field]*7) so you have used '7' is this for 7 days or 7 weeks.

                 

                3] can i use sales date instead of parameter.

                 

                I have to create same view but i need to see just 2 week sales Current and Previous week sales and same weeks of Previous year sales.

                 

                Thanks

                 

                • 5. Re: Year over Year Like for Like Comparison by Week
                  rahul.patel.2

                  Tony I am new to Tableau can you please tell me why have you used the following as i have doubt.

                  1] 0.49 in calculation (First Calculated Field (Segmenting out the YoY rolling weeks))

                    

                  2]And now that we are in 2016 will this 0.49 change?

                   

                  2]Third Calculated Field (Defining the dates):

                  [Parameter Date]+([First Calculated Field]*7) so you have used '7' is this for 7 days or 7 weeks.

                   

                  3] can i use sales date instead of parameter.

                   

                  I have to create same view but i need to see just 2 week sales Current and Previous week sales and same weeks of Previous year sales.

                   

                  Thanks

                   

                  • 6. Re: Year over Year Like for Like Comparison by Week
                    Tony Gruebner

                    Hi Rahul,

                     

                    In terms of your questions:

                     

                    1) The reason 0.49 is used is because Tableau doesn't have a 'RoundUp' function.

                     

                    For example, obviously day 1 would be in week 1. But if you did =Round(1/7) to get the week number you would end up with 0 and not 1. By making the formula =Round((1+0.49) / 7) you end up returning the answer 1 as we wanted. Same is true for Day 2, Day 3 and so on. Basically, it's an inelegant way of doing RoundUp.

                     

                    2) The formula works fine for 2016 because the 0.49 has nothing to do with what year it is.

                     

                    3) You could use Max(sales date) as a poster above has mentioned

                     

                    Hope this helps

                    • 8. Re: Year over Year Like for Like Comparison by Week
                      rahul.patel.2

                      Hi Tony,

                       

                      Can you help me out. i have to create similar report but just for two weeks. but i don't need current week just past two weeks and same week of last year. I tried editing the calculations you gave but i am not getting in correct way. (for eg- if week 4 of 2016) is going on i need to show (week 3 and 2 respectively) and same week of 2015. i am not using parameter dates i am using order date.

                      The other issue i need to add this to existing report and past developer had used date range summary and view type as 1]daily 2]weekly 3]monthly 4]quarterly 5] yearly. So i need to update your calculation in the existing one.

                      Attaching sample workbook so that you get what i am trying to say and can check the calculation and try updating it. Theirs a field called Date Range Summary. also date rang summary is used as filter but i could not add this into filter in sample work book.

                       

                      Thanks