7 Replies Latest reply on May 25, 2012 9:58 AM by Jonathan Drummey

    Dynamic YoY calc using input from parameters

    Martin Luxhøj

      Hi guys,

       

      Ok so I hope someone in this beautiful forum can find the time to help me out. I haven’t done much date calculation before so this post might be trivial to some of you hardcore users but please bear with me. Ok to the point:

       

      I would very much appreciate help with some dynamic YoY calculations. I was thinking that I would have my dashboard user select a year and a month or a range of both thru a parameter and then the YoY was calculated.

       

      I hope that this is enough to go by but if not I can elaborate. The perfect answer would explain the different elements of the calculation. This could even help other users out as I can see that there are a lot of questions about these YoY calculations.

       

      Thanks,

       

      //Martin

        • 1. Re: Dynamic YoY calc using input from parameters
          sirajmandayippurath

          Hi Martin,

           

          There is a feature guide document available from Tableau on this one. This shows how you could use date filters to create YoY calculations.

           

          http://downloads.tableausoftware.com/quickstart/feature-guides/relative_dates.pdf

           

          Is this what you were looking for?

           

          Cheers

          Siraj

          • 2. Re: Dynamic YoY calc using input from parameters
            Martin Luxhøj

            Hi Siraj,

             

            Thank you for your answer and my apologies for my late response.

             

            I was aware of this option and it is not what I'm looking for. I'm looking for something along the way of:

             

            If chosen year = X then YoY = X-1

             

            So the relative date par of it is what I need but I want to use table calculations based on parameter inputs. I know this is something a lot of people are asking about in this forum but I haven't been able to find a thread that answers my question to a tee.

             

            This thread http://community.tableau.com/message/160500#160500 and .Skimseys comment about augmenting the data set using an UPDATE query might be close or this http://community.tableau.com/message/141993#141993

             

            Thank you,

             

            //Martin

            • 3. Re: Dynamic YoY calc using input from parameters
              Jonathan Drummey

              Hi Martin,

               

              Here's one way to do this:

               

              - Create a year parameter, I called it "Choose Year":

              - Create a Year Filter calculated field with a function like

               

              IF YEAR([Order Date]) = YEAR([Choose Year]) OR YEAR([Order Date]) = YEAR([Choose Year]) - 1 THEN

                  1

              ELSE

                  0

              END

               

              - Drag the Year Filter to the Filter shelf and filter for 1. This is a useful thing to do for performance because table calculations operate after row-level and aggregate filters return data from the DB, so this filter reduces the amount of data that Tableau is returning.

              - For the YoY calc, I dragged Order Date onto the Columns shelf, SUM([Sales]) onto the text shelf and just used the built in Quick Table Calculation for YoY Growth for SUM([Sales]).

               

              See the attached workbook for an example.

               

              Cheers,

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Dynamic YoY calc using input from parameters
                Martin Luxhøj

                Jonathan,

                 

                This is GREAT! Thanks a million!

                 

                How do you come up with these things? Is there some kind of course I can attend to learn logic like this?

                 

                //Martin

                • 5. Re: Dynamic YoY calc using input from parameters
                  Jonathan Drummey

                  You're welcome!

                   

                  Unfortunately, I don't know of any course in particular that teaches this kind of logic. I imagine that Joe Mako's one day seminar will do at least some things along these lines, he's offering it in New York City on June 26.

                   

                  For me, having a useful theoretical model is important, so  have an approach where I think of the incoming table(s) of data as a canvas (like a painter's canvas) that underlies the visualization. This lowest level is what we see when we right-click on the data source and click View Data. What we see in the view is driven by the discrete (blue pill) and continuous (green pill) dimensions/measures in the view i.e. the Rows and Columns shelves, the Marks Card, and Measure Values if it is being used. The blue pills generate headers, the green pills generate axes. Based on what is in the view and the groupings/partitioning of the green pills that are defined by the blue pills Tableau automatically applies aggregate functions like SUM(), COUNT(), etc. to the green pills.

                   

                  Where things get really interesting is that calculated fields can be used to algorithmically modify/refine/adjust data to do what we need. In cases like the YoY view that you needed, we use parameters to give us sets of values that live outside the data to use in the calculations. The Year Filter calc is a way of using the data to create a value for Tableau to filter on, so instead of directly filtering the raw data Tableau is filtering the results of the calculation. That abstraction gives us a lot of power.

                   

                  We get even more power from table calculations. Table calculations are magic faerie dust that let us take the results from one set of values (rows) in the data and use them in calculating over other rows in the data. In more advanced work with table calculations, the results can appear to be be highly independent of what is on the Rows and Columns shelves, since table calculations can be nested and we can make use of the Level of Detail shelf. To go back to the canvas metaphor, Tableau's aggregate functions and table calculations let us have a variety of intermediate "canvases" before we get to the final view. Since Tableau does not completely expose all of those intermediate workings, mastering this aspect of Tableau is about building understanding (which is a continuous process for me) of how Tableau works, and building skills in holding the data structure and the intermediate calculations in your head in order to conceptualize the solution.

                   

                  Ok, that's the philosophical digression, on a more practical note, they way I got to where I'm at is practice, practice, practice. I've read the manual and followed the steps in it, used many of the online tutorials, read a number of Tableau blogs, and I've been active here on the forums. I use the forums in three ways: 1) learn by seeing what other people did. 2) learn by taking a solved question and trying to come up with my own solution, with the known working solution to check what I did. 3) learning by taking unsolved questions and coming up with solutions, and trying to make sure those solutions really work for the original poster. By consciously choosing to be exposed to a wide variety of situations (not just what I see in my day to day work), I've learned a ton, I'm learning something new most every day.

                   

                  For example, I chose to work on your post because I feel like I'm still mastering YoY calcs. I didn't know exactly how I was going to offer you a solution until I got into Tableau and started mucking around. I knew from past experience that a standard filter was not likely to work because those filters reduce the data (the size of the canvas) and for YoY calcs we need to have more of the data available to the view. So that led me to using a parameter. Once I had that, the rest was pretty easy, since I have spent time going through Tableau's built-in table calculations to understand how they work and how they change when the view changes. If you're interested in learning table calcs, here's a post I wrote the other day: http://community.tableau.com/message/179215#179215

                   

                  I hope this is of use to you, thanks for asking!

                   

                  Jonathan

                  • 6. Re: Dynamic YoY calc using input from parameters
                    Martin Luxhøj

                    Hi Jonathan,

                     

                    Wow…thank you very much for your very elaborate answer to my question it is very much appreciated! I try to use the forums for finding answers to my questions and I also try to dissect Tableau Public workbooks I have pulled from the Internet to try and learn from the technique the author used. This is very time consuming and I tend to get frustrated because I find my learning curve to be WAY too flat

                     

                    But once and again I find people online, like you, who are very generous with their knowledge and I get a spike on my curve. Thanks!

                     

                    I think part of my problem is that I don’t have a programming or data analyst back ground. If I had that I would have more of a concept of the logic that is needed to do these calculations…well this is at least what I tell myself

                     

                    //Martin

                    • 7. Re: Dynamic YoY calc using input from parameters
                      Jonathan Drummey

                      You're welcome!

                       

                      I imagine that taking some time to learn SQL could be really helpful for you, you'd learn about organizing data, databases, and some coding logic all at once, and an awful lot of what you learned would be very directly applicable when working with Tableau. Also, the Tableau logs show you the SQL that Tableau is using, so you could analyze that within the visualizations you are building.

                       

                      Jonathan