4 Replies Latest reply on Sep 5, 2017 9:19 AM by Jim Dehner

    Year-over-year with a % change

    Tim Cady

      I'm working on automating one of my manual excel reports with Tableau but I can't seem to get the calculations right. Currently, my excel report will take the sum of several different mesaures and calculate a % change based on  that.

       

      Here's that I'm trying to do:

       

      20162017% Change
      KPI1

      $100,000

      $97,000-3.00%
      KPI2$35,000$42,00020.00%
      KPI3$20,000$21,0005.00%

       

      What I'm after is recreating the % change. The years are being seperated by a Call Date. I've started putting together calculated fields for each KPI but was worried that will get too confusing. Is there a table calculation I can apply to calculate all rows?

        • 1. Re: Year-over-year with a % change
          Matt Lutton

          Post a sample TWBX with what you currently have, and we can easily show you how to apply a YoY change Table Calculation to the view.  Its difficult to provide help without seeing any data/workbook, but you can use the Sample Superstore datasets that ship with Tableau, or your own randomized subset of data to ask your question here on the Community Forums -- but a workbook will allow volunteers to see/understand your data, and apply the help you need.  At first glance, it seems like a simple Percent Difference table calculation, but its hard to know for sure without seeing a .TWBX file.

           

          Table Calculation Type: Percent Difference From

          Business AnalyticsYear on Year calculation in Tableau

          • 2. Re: Year-over-year with a % change
            Marc-Anthony Di Biase

            You can use an LOD calculation to calculate the columns by year, however I believe the level of detail you are after aggregates the KPI number and the Year together. In this case, your data is not structured optimally, and I would recommend pivoting your data such that you have a field called 'KPI' which would hold the values '1', '2' and '3'. This should also be applied to the date fields (if they are not already pivoted) such that you have a field for 'Date' or 'Year' that holds the values '2016' and '2017' (and all values moving forwards)

             

            Here is a guide to help you with pivoting: Pivot Data from Columns to Rows

             

            Once this is accomplished you can use the following formula:

            {FIXED [Year],[KPI] : sum([Sales])}

             

            Which will give you the sales (assumption that these are sales figure) value for each KPI for a given year.

             

            Let me know if this is what you were after or if I've missed the mark!

            • 3. Re: Year-over-year with a % change
              Jim Dehner

              Hi Tim yes you can do it with a table calculation or your can write your own

               

               

              The formula is

               

              (LOOKUP(sum([Sales]),0) - LOOKUP(sum([Sales]),-1) )/LOOKUP(sum([Sales]),-1)

               

              or you will get the same if you use the table calc

               

              Either way the results are the same

               

              The Grand Total option will take the formula and apply it to the totals row

               

              Jim

               

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              2 of 2 people found this helpful
              • 4. Re: Year-over-year with a % change
                Jim Dehner

                Thanks Ti,

                Glad to help out

                Jim