13 Replies Latest reply on Jul 6, 2016 6:42 AM by Patrik Berg

    The same day last year comp

    Sergey Levchenko

      I think it is pretty simple question but I'm stuck on it for a week.

      I'm trying to compare one day at this year to the same day at the last year. The main idea is so that everyday the data will be updated and at the dashboard you could see the table with yesterday's metric and the comp for the same day last year.

      I want to have 2 colums e.g. with 2011 and 2012 year as it is set up in the attachment. But if somebody has any ideas how it will be better or if it is doable in another way, I will be very pleased to see this comment.

      Great thanks and sorry for poor english :-)

        • 1. Re: The same day last year comp
          Alex Kerin

          Welcome to the forums Sergey- your English is excellent. One way to do this is through parameters. if you have data for today, you would do it a little differently (replace the parameter with today() ), but this is set up without that assumption.

          1 of 1 people found this helpful
          • 2. Re: The same day last year comp
            Sergey Levchenko

            Alex,

             

            Thank you, but here what is the problem. E.g. today is March 20, 2012 and the same day last year would be March 22, 2011.

            Your example is really useful but in case of different days I have no idea how to use it.

            Any thoughts about this point?

             

            Thanks

            • 3. Re: The same day last year comp
              Richard Leeke

              Here's a way that seems to work using a parameter and a few date calculations.

               

              Basically I've worked out the offset between years needed to get the same day of the week and then worked out the nearest equivalent day number in the year. This approach copes with 29th February this year, too!

              '

              2 of 2 people found this helpful
              • 4. Re: The same day last year comp
                Sergey Levchenko

                Richard,

                 

                This is what I actually need. This approach is really awesome!

                Thank you very much!

                • 5. Re: The same day last year comp
                  Sanjay Pulickal

                  Hi ,

                   

                  Thanks for giving me idea to solve a similar  issue

                   

                  Another approach

                   

                  1. Created a calculated field    YTD based on formula

                   

                  if datepart('dayofyear', [Period]) <=  datepart('dayofyear', TODAY())

                  then 'YTD' else 'Remaining Days' end

                   

                  [Period] is a  date field and TODAY() is an inbuilt function.

                   

                  2. Created a user filter based on calculated field YTD.

                   

                   

                  Thanks

                  • 6. Re: The same day last year comp
                    Fred Arve Fahre

                    Hi!

                     

                    Thanks for this - it got me one (big) step closer to what i was hoping to achieve.

                    I want to be able to compare the last x days (say 7 days then) with the same weekdays the year before. So exactly like the answer here (adjusted slightly to get 2012 vs 2013), but not only for one day, but for several days. I was hoping to be able to create a simple line graph with sales per day, this year vs last year, weekday (even better, this years dates)on x axis. E.g Monday 22 oct 2012 vs Monday 21 Oct 2013, Tuesday 23rd 2012 vs Tuesday 22nd 2013 etc.

                     

                    So the information output would be something like this: Last friday was better than the (comparable day) year before, but in the weekend we had a decline. The first couple of days this week however we have started better than last year.

                     

                    So I have to be able to sort the weekdays (chronologically) on my x axis aswell, i assume that might be an issue if i can get the filtering right.. Any help greatly appreciated!

                     

                    Fred

                    • 7. Re: The same day last year comp
                      Themis A

                      Hello to All!!

                       

                      Im really new on tableau and im trying to compare all days of 5 continuous ISO Weeks with the same days from last year.

                      Im dont want to do it on a date to date basis (e.g 2nd of Dec this year with 2nd of Dec last year). For example i want to compare a range of dates from 25th Nov 2013 - 29th Dec 2013  with  the corresponding ones from last year 26th Nov 2012 - 30th Dec 2012.

                      The answer that Richard provided does it for one day only by using a parameter. Fred Arve Fahre you found a way of doing it for several days.

                       

                      Can you please provide more description on how you did that on tableau?

                       

                      Thanks

                      Themis

                      • 8. Re: The same day last year comp
                        Fred Arve Fahre

                        Hi Themis!

                         

                        I am looking back on that one now, and i am wondering a bit.. I started with base dates etc, used Richards example to get the weeks syncronized, but in the end i landed on a simple way of doing it. See the attached print if it helps (each color a year).

                         

                        I have a calculated field as a filter where one can chose weeks Weeks (US) = (datepart('week', [Date]). I wanted "Norwegian weeks", but during the last months i have chosen to use the US weeks, noone seem to care anyway as long as we are consistent. There is really nothing special to this, I did create a "Base date" parameter, but at the moment I dont use it. So maybe i didnt really solve same issue as you have here. YoY - day week.PNG.png

                        • 9. Re: The same day last year comp
                          Themis A

                          Hello Fred,

                           

                          Thanks a lot for this information.

                          It didnt quite solved my problem but gave me enough information to create the following analysis:

                           

                          The data are displayed correctly when i use weekdays, but when i want to add the numbers (e.g 4th or 5th of Jan) then it displays them differently. Ideally i would like to have 03/01/2013 & 02/01/2014 (same day this year) in one row which can only be done by using a formula. See example/problem below

                           

                          • 10. Re: The same day last year comp
                            stephanie.sims

                            Hello!

                             

                            I am also new to tableau and am looking for a way to do something similar, but I don't understand the formula enough to customize it for my case. I want to look at information from yesterday (or any date) and compare to the same weekday of last week. So for example if I wanted to look at data for Mon, Jan 26th, I would want to compare that data to Mon, Jan 19th. I want to be able to have that data in the same sheet so I can do some conditional formatting and have arrows (green, yellow, and red) to show if things are better, same or worse than the week before. Can anyone help with a formula for that or a way to achieve it? I appreciate any help I can get! Thank you!

                            • 11. Re: The same day last year comp
                              Aaron Garcia

                              To do this, you basically need to just add 364 days to any date that is not in the current year. I created a new calculated field called 'Yesterday' which is just (Today () -1)  I then created a new calculated field that i named 'Offset Dates'. Inside of that, I wrote this formula.

                               

                              IF ([Date])>=([Yesterday]-363)

                                      THEN DATEADD('day', 0, [Date])

                              ELSEIF ([Date])>=([Yesterday]-727) and ([Date])<=([Yesterday]-364)

                                      THEN DATEADD('day', 364, [Date])

                              ELSEIF ([Date])>=([Yesterday]-1091) and ([Date])<=([Yesterday]-727)

                                      THEN DATEADD('day', 728, [Date])

                              END

                               

                              You can then adjust your table to have the actual year at the top and the data will line up.  If you want a range that spans two different years (i.e. you are looking at the last 28 days but it's only Jan 14th) then you will need to make some additional adjustments. but this works well for a single day.

                              • 12. Re: The same day last year comp
                                Ashwin Chandak

                                I am wondering if you could help me out in this situation:

                                 

                                I have data that dates back to over 2 year and I am trying to get a YoY (Year-over Year) % change for the same day of the week. That is Sunday in the week of '15 should match to the Sunday of the week in '16. The quick table calculation is great but not of much use here.

                                 

                                I have been trying to do a difference in the value and the lookup of 364 days ago, but it renders null values.

                                 

                                My goal is to compare the same weekday for the same week. For example, how did my sales do on the Sunday of this week for the Sunday of the same week last yr.

                                 

                                Any ideas/workarounds will be much appreciated.

                                 

                                Thanks!

                                 

                                 

                                -Ashwin

                                • 13. Re: The same day last year comp
                                  Patrik Berg

                                  Hi Ashwin

                                   

                                  i'm trying to do the exact same thing, got it working as long as I show all dates. With a normal lookup table calc. -364

                                  But when I want to filter the months shown I loose the comparisson QTY.

                                   

                                  Here are 3 threads looking at this and similar.

                                  Please let me know if you find a solution.

                                   

                                  https://community.tableau.com/message/506901?et=watches.email.thread#506901

                                   

                                  Compare with a day last year

                                   

                                  Comparing 2 different periods on same data

                                   

                                  Regards

                                   

                                  Patrik Berg