3 Replies Latest reply on Jul 11, 2017 11:30 AM by Joe Oppelt

    Year Over Year Tooltip Date Calculation

    Nicholas Harris

      Hi,

       

      How would I calculate the YoY growth calculation below? The previous years may or may not be in view.  Need to show calculation after any filters.  Workbook and image attached. Many thanks!

       

        • 1. Re: Year Over Year Tooltip Date Calculation
          Joe Oppelt

          See attached.

           

          First of all, YOY difference can be done with a quick table calc.  Some of the backward-looking functions are already written for us.


          So I put sum(sales) on the detail shelf, and change it to quick table calc, and now there is a second SUM(sales) there.  I added it to the tooltip, and there it is.

           

          For the record, I usually just write my own table calc for stuff like that.  Often I want to use it elsewhere too.  Just my style though.

           

          But you need to have the prior year's data in the underlying table to do that sort of calc (whether self-written or using Tableau's.)  So if you do a quick filter to chop off years, a quick filter limits what is in the underlying table.

           

          But if you use a table calc to filter, it leaves the underlying table alone, and just controls what gets displayed in the viz.

           

          So see Sheet 2.

           

          I wrote a table calc called Select Year.  This uses LOOKUP, which is a table calc, to build the filter.  (There are countless other table calcs you could also use.)  My calc says this:  For the current record (offset zero in the syntax) what is the year of this date?  When used as a filter, you even get a list of the dates to choose from.  You can have one, two, all, whatever amount of the values on the list.  But the underlying table remains there, so Tableau has view of the data to look back at the prior year to do the DIFF calc with.

          • 2. Re: Year Over Year Tooltip Date Calculation
            Cristina Palo

            Hi Joe - how would I do if for example the volume for 2016 was only through the second week of July? I am trying to get the % change to show Jan-second week of July 2016 vs Jan-second week of July 2015....using the YOY calculation is not working since 2015 has way more data than 2016 in this example

             

            Any ideas?

             

            Thanks

             

            Cristina

            • 3. Re: Year Over Year Tooltip Date Calculation
              Joe Oppelt

              In the attached I created a calc that tells me the last date in any year.  See [Last order date in year].  You actually could have done this:

               

              { FIXED : MAX([Order Date]) }

               

              instead of this:

               

              { FIXED [Order Date Year] : MAX([Order Date]) }

               

              The second (which is in the example) tells you the last date per year.  The first calc just tells you the last date in the whole data source.

               

              On Sheet 2 I added that last date per year to the sheet, just to show you how it looks.

               

              Knowing the last date lets you leverage that, and grab up to the same date in the prior year so that you are comparing apples to apples.