1 Reply Latest reply on Nov 29, 2018 9:01 AM by Joe Oppelt

    Calculation for Better (Worse) Vs Prior Year

    Nicholas Tomanelli

      Hi All - My columns is 1 dimension "states", and my rows is 1 dimension "Years". As you can see  in the below snap shot, I only have two years of data 2017 and 2018.

       

      1)  I am looking to add a row calculation to show the difference between 2017 and 2018? I w

      2) could use a calc to do a sum of 2018 only.

       

      Thanks

        • 1. Re: Calculation for Better (Worse) Vs Prior Year
          Joe Oppelt

          Tableau doesn't add rows that aren't already in the table.

           

          If you had a [Year] value of "Difference" in the [Year] dimension (even if it is loaded with all nulls) then you could make a calc to carry the actual values forward, and load the "Difference" cell with the difference.


          This can get messy in your data source though.

           

          Tableau had an automated TOTALS function.  You could set it to SUM or some other function, but override it under some conditions to load your own value into it.  If your actual data values (such as 229,380) are just simple measure values, you can probably make this work, but if they are aggregate values, I generally don't have success with this method.

           

          The calc to do this would look something like this:

           

          (Assume the pill to display your data is SUM([Sales]).)

           

          IF FIRST() = LAST() then [your calc to do the difference between the two years]

          ELSE

          SUM([Sales])

          END

           

          Turn on totals, and the FIRST=LAST will override what Tableau wants to load in the TOTALS row.  Display that calc instead of the current SUM([Sales]) pill.

           

          If all that fails, I create a second sheet that is only the totals line, and I display it adjacent to the actual data sheet on a dashboard to look like one viz.