1 2 3 Previous Next 42 Replies Latest reply on Jun 2, 2016 8:47 AM by Archit Sood Branched to a new discussion.

    Comparing 2 different periods on same data

    Shane Steward

      Hello friends,

       

      For a couple days now I have been trying to come up with a solution to my problem but I cannot seem to find the answer.  Maybe one of you can help.

       

      In summary, I have a set of data which contains Date, Spend ($$), and Clicks (#).  On a single chart I want to be able to display the total Spend and Clicks for two different periods, however, I have 4 different period comparisons I need to handle; displaying the type of comparison based on user choice.

       

      1)    Week over week (any week of any year)

        a.    12/1/10 – 12/7/10 over 12/1/11 – 12/7/11 (different years)

        b.    7/4/11 to 7/10/11 over 7/11/11 to 7/17/11 (same year)

       

      2)    Month over month (any month of any year)

        a.    Aug 2010 over Aug 2011 (same month over different years)

        b.    Aug 2011 over Sept 2011 (consecutive months same year)

       

      3)    QTR over QTR

        a.    Q1 2010 over Q2 2010 (consecutive quarters, same year)

        b.    Q1 2010 over Q1 2011 (different year, same quarter)

       

      4)    Year over year (any year over any year)

        a.    2010 over 2011

       

       

      What are your suggestions and ideas for how to handle this?  Is it even feasible to filter the same data on two different periods?

       

      Thanks,

      Shane S.

       

      P.S. I am working with Tableau v6.0

        • 1. Re: Comparing 2 different periods on same data
          Joe Mako

          There are many options when it comes to dealing with date calculations in Tableau. Attached is an example. You will want to think about how you want to deal with weeks, I did not make any allowances in the attached, and you can see that the same week is in both years, There are options to deal with this, it all depends on your business logic.

          5 of 5 people found this helpful
          • 2. Re: Comparing 2 different periods on same data
            guest contributor

            Hi,

             

            Can i have the same twbx in tableau 6.0 version since i also require similar solution for one of my reports?

             

            Thanks & Regards,

            Sushma K

            • 3. Re: Comparing 2 different periods on same data
              Joe Mako

              Sushma, Although I recommend using the most current version of Tableau, you can run both 6.0 and 6.1 on the same computer at the same time. I do not plan on remaking this workbook in an earlier version.

              • 4. Re: Comparing 2 different periods on same data
                James Cartwright

                Hi Everyone!

                First post, and a complete newbie to Tableau so bear with me...

                I think this thread is relevant to what i want to achieve, but i can't quite work out how to tweak the workbook to answer my specific questions which are:

                 

                1. Measure MTD, QTD, and YTD vs. MTD-1, QTD-1 and YTD-1 for all my metrics (plus looking at this thread also being able to compare against previous period as opposed to period - 1 year)

                2. Ensure that when comparing against previous periods, only summing upto the current date in the month (e.g. upto 25th October 2011 vs. upto 25th October 2010 for MTD, or from 1st October, upto 25th October 2011 Q4 2011 vs. same date range for 2010)

                3. Be able to view all metrics at once (currently only 2 metrics in data BR and TD, set up as parameter to view by model)

                4. Ideally be able to show traffic light of Month on Month, Quarter on Quarter etc - can't see how to do this without creating separate tables for these measures.

                 

                See the attached for what I have managed so far. This approach uses the MTD etc measures to show the period comparisons, with the metric parameter coded within the calculation. This limits me to showing only one metric at a time.

                 

                Sorry for long post, but I am a bit stuck, and know there must be a better way to achieve my goal

                1 of 1 people found this helpful
                • 5. Re: Comparing 2 different periods on same data
                  Emily Mueller

                  Joe- would it be possible to show these week over week comparisons on a line graph?

                  • 6. Re: Comparing 2 different periods on same data
                    arthi.keat

                    Thank you Joe. The worksheet has been very helpful.

                     

                    I was trying to recreate the same worksheet but for some reason I am not able to get any values in the "Selected Over Text". Can you pleae help look into it and see what/where I am missing some thing.

                     

                    Thanks for your time..

                    • 7. Re: Comparing 2 different periods on same data
                      Shawn Wallwork

                      Since Jonathan just help me understand one of Joe's workbooks that used nested table calculations it's only fitting I pay this one forward.

                       

                      Arthi, when you see a table calc that doesn't have any compute using checked:

                       

                      Nested-1.png

                       

                      Then you know either it is an "Advanced" compute using or it is a nested table calculation or both. In this case it is both. The first one is a straight up compute using 'Year':

                       

                      Image-2.png

                       

                      But the other one (the one you probably missed) was the "hidden" one:

                       

                      Nest-3.png

                       

                      And when you look at "Previous Text" you'll see he has set up an advanced compute using:

                       

                      Nest-4.png

                       

                      And that Advanced looks like this:

                       

                      Nest-5.png

                      The order of the Compute Using pane can at times make a difference, so if you're following one of Joe's workbooks make sure you sort them the same way. (See attached.)

                       

                      --Shawn

                      1 of 1 people found this helpful
                      • 8. Re: Comparing 2 different periods on same data
                        arthi.keat

                        Thank You so much Shawn. I would have never figured it out. I got this one down.

                         

                        Can you help me answer one more question. Data shows up only when we select 'Month' in "Select Date Level". But is null for other selections. Would you be able to help me on this one..

                         

                        Thanks in advance for your time n help...

                        • 9. Re: Comparing 2 different periods on same data
                          arthi.keat

                          Got it..

                           

                          I missed setting Table Calculations for all..

                           

                          Thanks for your help..

                          • 10. Re: Comparing 2 different periods on same data
                            Joe Mako

                            To expand on Shawn's comment

                            The order of the Compute Using pane can at times make a difference

                             

                            The sort order of dimension pills in the Compute using list box on Advanced dialog makes a difference in these situations:
                            - Order Along is Automatic
                            or
                            - At the Level is not "Deepest", and not the last one in the list, eg "Date Level" in this screen shot (two options in the drop-down mean the same thing)
                            or
                            - Restarting every is not "None"

                             

                            And said the other way:

                             

                            The sort order of the dimension pills in the Compute using list box on Advanced dialog does not make a difference in this situation:
                            - Order Along is specified
                            and
                            - At the Level is "Deepest", or the last one in the list, eg "Date Level" in this screen shot (two options in the drop-down mean the same thing)
                            and
                            - Restarting every is "None"

                             

                            Also depending on the table calculation in use, formula logic, and your data contents will impact if you see a noticeable difference or not.

                             

                            So in short, Shawn is correct, "The order of the Compute Using pane can at times make a difference."

                            1 of 1 people found this helpful
                            • 11. Re: Comparing 2 different periods on same data
                              chaitanya kaita

                              Hi Joe,

                               

                              I am a new Tableau user and have been asked for some complicated dashboards.

                              The requirement is to compare Last 4 weeks from last saturday  VS Prior 4 weeks for current year and last year.

                               

                              Below are some date parameter examples ( all are compred against last year)

                              L4W Vs P4W

                              LW Vs PW

                              Last Month Vs Prior Month

                               

                              I have followed the same in my workbook but a little confused. Could you please help me figure out.

                               

                              Attached is the workbook.

                              • 12. Re: Comparing 2 different periods on same data
                                Garry Williamson

                                I rebuilt some  of this on my own in a new  workbook to learn, but i dont get why the year ago clicks and the previous clicks both have the same formula: Lookup(sum([clicks]),-1)

                                 

                                That logic for some reason is escaping me.

                                 

                                Thanks

                                Garry

                                • 13. Re: Comparing 2 different periods on same data
                                  Elad Lanir

                                  Hi Joe,

                                  First, thank you for your time and neat solution (and steps).

                                   

                                  I managed to re-create your solution on my data and it works great! But I did encounter one issue that I'm not sure how I can solve: on the row shelf, I added another dimension for which I want to display the WoW calculation per that dimension value, and the user can select from a filter the period of comparison he wants to display.

                                  img.png

                                  The problem begins when one of the values has data for current week but no data for last week, while all other values do have data for last week. In that case, the 'Selected over text' dimension has two consecutive values because, for the one value with no data on last week, the logic of (lookup (<measure>, -1)) will return 2 weeks ago's data and the  'Selected over text'  will look like this:

                                  img1.png

                                   

                                  I guess  that for those values who had data this week and no data last week, my expectation was to see '0' on the 'Compare to' column.

                                   

                                  Any idea how can I solve this tweak?

                                  1 of 1 people found this helpful
                                  • 14. Re: Comparing 2 different periods on same data
                                    Joe Mako

                                    Can you please provide a sample packaged workbook with data that represents your situation, and detail what you expect for results and interaction? Thank you.

                                    1 2 3 Previous Next