8 Replies Latest reply on Jan 17, 2017 9:58 PM by Ravi Patel

    Comparing Multiple Years of Data to a Single Year

    Valentino Elliott

      Has anyone figured out a way to compare current year data to the previous 3 years in the same dashboard? See below for an example of what I am seeking to display. Any help will be greatly appreciated.

       

       

       

      NameCurrent Year Win % (2017)Previous 3 years Win % (2014, 2015, 2016)
      Bob95%87%
      John58%75%
        • 1. Re: Comparing Multiple Years of Data to a Single Year
          Stephen Rizzo

          I can think of several ways to do this. The first one I would recommend looking into is Tableau's grouping feature. See attached workbook for an example. In the example workbook, win percentage by year was pre-calculated in the data source, so an average for the three previous years is displayed instead of the three-year win percentage. If your data is not pre-aggregated, you can use this same approach to get the three-year win percentage instead.

          • 2. Re: Comparing Multiple Years of Data to a Single Year
            Jamieson Christian

            Valentino,

             

            You will want to employ a Moving Calculation. See this online help article:

             

            Table Calculation Type: Moving Calculation

             

            Your application is a bit more specific, since you want to do the moving calculation against data leading up to but not including the current record. So you'll probably also need to incorporate a LOOKUP() table calc to get to the correct moving average. If you post a packaged workbook, it will make it easier to provide a specific example for your data. But see if the above article puts you on the right path to a solution.

            • 3. Re: Comparing Multiple Years of Data to a Single Year
              Valentino Elliott

              Stephen,

               

              Thanks for your response. I cannot open your workbook because I have Tableau 10.0. I received the following message:

               

              "This file was created by a newer version of Tableau. Please contact Tableau Software to upgrade your version."

              • 4. Re: Comparing Multiple Years of Data to a Single Year
                Stephen Rizzo

                Here is a copy of the workbook compatible with v10.0. In the future I would encourage you to include a sample Tableau workbook and your Tableau version number with your post if possible - that will help us get a better sense for how your data is structured and which version we should use to create sample workbooks that you can open.

                • 5. Re: Comparing Multiple Years of Data to a Single Year
                  Valentino Elliott

                  Thank you for the suggestion, Stephen. My apologies for not being specific enough. I am attaching sample data that is structured similarly to the data I'm working with.

                   

                  I am trying to compare Win Percentages for 2013 to 2011, 2012, and 2013. In my initial post, I mistakenly stated I was comparing current year to the previous three years. The ideal result would be a table similar to the one below.

                   

                  PositionGames2013 Conference Win %2013 Countrywide Win %2011-2013 Conference Win %
                  2011-2013 Countrywide Win %
                  QB
                  WR
                  RB
                  TE

                   

                  Thanks so much for your help.

                  • 6. Re: Comparing Multiple Years of Data to a Single Year
                    Joe Oppelt

                    Valentino -- You can always install the latest version of Tableau on your desktop with your current version.  (I have all the versions from 8.2 through 10.1 installed on mine!)

                    • 7. Re: Comparing Multiple Years of Data to a Single Year
                      Jamieson Christian

                      Valentino,

                       

                      I set up some calcs in a copy of your workbook, but since I'm on 10.1.3 and you won't be able to read the workbook, I'll just describe what I did.

                       

                      First, your have a lot of LOD expressions that I think have created a minefield for you to navigate, and in some cases are producing erroneous results. For instance:

                       

                      [Countrywide Games]

                      {fixed  [Position]:SUM([College wins])+SUM([College losses])}

                       

                      This calc does not include [Year] in the LOD, so it's going to be the same for every year in your report. You have several LOD calculations based on { FIXED [Position] } that similarly yield erroneous results. And since your %'s are based on these calculations, they're wrong too. You have some cleanup to do.

                       

                      Now, assuming you've fixed the existing calcs and they are now valid, here are the additional calcs I created. Both of these are configured to Compute Along [Year].

                       

                       

                      [Conference Win % (last 3 yrs)]

                      WINDOW_SUM(SUM([College wins]),0,2)/WINDOW_SUM([Games],0,2)

                       

                      [Countrywide Win % (last 3 yrs)]

                      WINDOW_SUM(SUM([Countrywide Wins]),0,2)/WINDOW_SUM(SUM([Countrywide Games]),0,2)

                       

                       

                      Add [Year] to COLUMNS shelf on your view (before Measure Names), and sort it Descending, so that 2013 is first. Then add the above calcs to your Measure Values shelf.

                       

                      This should give you the numbers you need for each year. If you just want to see the latest year, add the following Table Calc Filter to your Filters shelf (also compute along [Year]).

                       

                      INDEX()=0

                       

                      Hope this helps!

                      • 8. Re: Comparing Multiple Years of Data to a Single Year
                        Ravi Patel

                        You can create following two calculated fields to achieve your goal:

                         

                        1) Current Year Win%  =  IF YEAR(TODAY()) = [Year] THEN [Win%] END

                        2) Previous 3 Years Win% = (SUM([Win%]) - SUM([Current Year Win%]))/(COUNTD([Year]) - 1)