3 Replies Latest reply on Dec 6, 2016 12:51 PM by Matthew Risley

    YoY Calculation

    Matthew Risley

      Guys... I'm having a major brain fart and I need your help.

       

      My requirement is to show a bar chart of the difference between the most recent year vs last year for each Business.

       

       

      So the bar chart for Bakery should be -2.4 (-2.9 —  -.5). Call center's bar should be 13 ( 27.4 — 14.4) so on and so forth.

       

      I know this is super simple, right?

       

      For some reason I was thinking this should work: Making two calculated fields:  Year(Date) = 2015 and Year(Date) = 2016 and then doing a fixed LOD.

       

      { FIXED [year 2016] : [Score] } - { FIXED [year 2015] : [Score] }

       

      Today is not my day and I'm drawing a blank on this simple answer. Thanks for the help.

       

      (Using Tableau 9.3)

        • 1. Re: YoY Calculation
          Matthew Risley

          Answering my own question. I knew it was a brain fart:

          Using a table calc:
          ZN([Score]) - LOOKUP(ZN([Score]), -1)

          and Compute Using Date.

           

          However, I'd be interested to see if there's another calculation without the need for a table calc. I'll leave this open

          • 2. Re: YoY Calculation
            Jonathan Drummey

            Hi Matthew,

             

            Since this is the formula of the difference from prior quick table calculation that's the fastest way to build it out (just a few clicks) and will have the fastest performance (since it's a computation in Tableau on the marks in the view).

             

            The other options are:

             

            1) use a Measure Names/Values crosstab when there's a measure for each year. This could be built out via regular aggregates or Level of Detail expressions (or both), but would require creating a new measure each new year.

             

            2) use a measure built using Level of Detail expressions that is essentially a complicated SUMIF.

             

            3) use a Tableau data blend where the linking dimensions are the Business and Year, only the year is offset in the secondary source so via the blend you get the prior year and current year.

             

            4) use Custom SQL (if available) do a self-join on Business and Year, where the year is offset. In Tableau v10.2 (in beta) we will be able to do this without Custom SQL using the new Join on Calculations feature.

             

            The last two years at the Tableau Conference there have been sessions on "which kind of calculation should I use" and the starting place is if the data exists in the view then a table calculation is typically the place to start, if the data doesn't exist in the view (i.e. you want a calculation at a finer grain). You can find the TC16 sessions at http://tclive.tableau.com, I don't have the names offhand.

             

            Jonathan

            2 of 2 people found this helpful
            • 3. Re: YoY Calculation
              Matthew Risley

              Johnathan,

               

              Would you be willing to walk us through Options 1 and 2?

               

              I want to respect your time, but that would be incredibly helpful.

               

              Also +1,000 for that link. Didn't know that was an option. There are tons of videos, but I'll look for this topic.

               

              Best,

              --Matt