10 Replies Latest reply on Dec 31, 2014 7:11 AM by Tim Sheridan

    Year Over Year Two Ways - Assistance Requested

    chris.linderwell

      Hi Community,

       

      I have been all over this forum the last 24 hours looking for some intelligent ways to do YoY calculations. I have narrowed it down to the fact that i have to use parameters and calculated metrics but the rest is absolutely hellish to figure out for the first time. I'm sure after i get it once it won't be bad.

       

      I have two situations where i need YoY.

       

      First

      I need to create graphs like the one below where the current year is based on the a date range quick filter. It is important to note this needs to be based off of a fiscal calendar so August 23 of this year (2013) will be compared against August 24th 2012 - the same comparable date. I will end up having several of these for different metrics.

       

      YoY graph.png

       

      Second

      I need to be able to create a table that will sit below this set of trended graphs where at the very least i have a static YoY delta for the same period of time i am looking at in the data table (revenue, orders, etc.).  Ideally i could have a parameter modify the data i am looking at by changing the data and delta to be a daily, weekly, and monthly view - but i would call that a nice to have.

      YoY Table.png

       

      Unfortunately, due to NDA i can't share a workbook which only makes this more of a challenge but attached is a sample data set for one day. In the end it will hopefully look something like below - the trends would be aggregation of all marketing channels, the bottom would be a table that had multiple marketing channels (more than just paid search).

      Total.png

       

      Thank you in advance for any assistance.

        • 1. Re: Year Over Year Two Ways - Assistance Requested
          Wilson Po

          Hi Chris,

           

          Both things should be fairly easy in Tableau, but simply require knowing where to look for the right set up.  I went ahead and used Tableau sample data simply because there wasn't much of a range of dates to do much with in the sample you attached. Let's break them down:

           

          1)  In you first YoY line trend, it seems like you are simply overlaying the two year against one another on a common time series and differentiating the two years with colors.  That being said, the same idea need to be applied in the Tableau UI - where our year field (I suspect this is your [Fiscal Year] field) needs to be applied to the color shelf, while you measure sits on the row shelf (think Y-axis) and the key of course is to use a date field that is not year specific for you columns shelf.  With this you should be able to overlay the two lines on top of one another and compare the two years against one another.  In my example, I also added in a relative date filter - which can be handy if my data sets contains more than two years and I only want this year and last year data.

           

          2) With doing YoY growths, this is generally done best using Table Calculations. There's actually a quick table calculation for YoY growth with any measure that is currently on one of the shelves that is accessible through right click. The only caveat is that this is defined by how the view is laid out, and so it is critical not filter or exclude necessary information in order for it to compute. Instead, hiding a row will allow for the table calculation to still work, while only showing just the current year information. 

           

           

          Hope this helps move things along.

           

          Wilson

          1 of 1 people found this helpful
          • 2. Re: Year Over Year Two Ways - Assistance Requested
            chris.linderwell

            This is helpful in the right direction - apologies for not providing a wealth of data. I think your stacked trend lines are what i am trying to accomplish. I have been fortunate to have some side bars with Joe Mako who has helped me get to the point of knowing i needed to create some fields with my data lookup that normalize day numbers instead of dates and keep a running epoch count. The issue i am running into now is creating the parameters that specify if the x-axis is based on days/weeks/months and then switching out the day/week/month number with an actual date/week beginning date/month name so that the graphs make sense.

             

            I believe Joe and I are going to chat later tonight which may yield a solution or some follow ups.

             

            I believe a combination of these threads is what i need, but they don't explain the methods to their solution well enough for me to replicate

             

            http://community.tableau.com/thread/118707?start=0&tstart=0

             

            http://community.tableau.com/thread/127607

             

            Thanks!

            Chris

            • 3. Re: Year Over Year Two Ways - Assistance Requested
              Joe Mako

              The situation is interesting because the data is stored in normal calendar dates, but the interaction and analysis needs to happen with a custom Fiscal Retail calendar, and with a kind of relative date filter based on this custom calendar. If there was no need for the custom calendar, this would be fairly straightforward like Wilson detailed.

               

              Here is a quick rundown of the techniques used in the attached workbook :

              - Connection to Value table as primary, as a kind of scaffold data source (it is a lookup table for conversion of Date to Fiscal Retail levels along with length from Epoch at multiple levels and Year Ago Date)

              - Secondary connection to Value table, blended on a "Blend" field, thanks Jonathan Drummey

              - Filter on secondary Value table to keep only selected date (from parameter), bringing the epoch value across the blend

              - Aggregated Filter in primary to keep dates in range (from parameter)

              - Dimensions for the worksheet are Date (for aggregation filters and axis) on the Columns and Date Level (computes the level of detail desired) on the Path shelf

              - Connect to the actual data source twice, once to blend on Date, and again to blend on Year Ago Date

              - Table calculations for aggregation to desired level of detail, and filtering out extra marks (compute using for all table calcs is an advanced with both Dimensions used for Addressing, and Restarting Every set to Date Level)

              - For each additional metric, duplicate and edit "Value" and "Value Year Ago" calculated fields as needed

               

              This is a very interesting use case for working with a data blends and a custom retail calendar in Tableau.

              • 4. Re: Year Over Year Two Ways - Assistance Requested
                Matt Lutton

                It would be nice to have a place on the forum where really interesting use cases/solutions like this were compiled for reference.  This is just really good stuff, and I hate to see it disappear into oblivion (I know, I could keep a list myself...)

                • 5. Re: Year Over Year Two Ways - Assistance Requested
                  chris.linderwell

                  Absolutely terrific solution to my problem. I'd also like to publicly thank him for spending some time on the phone with me, on a Sunday, to work through the solution. He even let me record the solution so I could go back through and monitor step by step what he did. It had taken my 15 hours of hacking around to get no where and he did this whole thing in a matter of hours.

                   

                  True Tableau Expert with a passion for solving tough problems. This should work well for any client with a retail calendar

                  • 6. Re: Year Over Year Two Ways - Assistance Requested
                    Steve Donovan

                    Thank You Joe et al,

                     

                    This is exactly the same scenario I need to resolve to get to sales metrics like %increase over "same day last year" which is financial in nature (first Thursday kind of deal).

                     

                    I come from a database background and am new to Tableau, but I think I follow the technique.   If you will excuse my database centric viewpoint, I wanted to confirm my understanding.

                     

                    The Date files you bring in (excel or otherwise) are effectively a DATE dimension table in a data warehouse that provide the mapping needed to convert from an actual date (date_key) to the financial equivalent.   This same file/table contains the dates (keys) for the same_day_last_month and same_day_last_year.   File or DB table DATE_DIM does not matter the concept holds.  

                     

                    Then also like on the pure SQL side, you need multiple copies of the data (aliases).   Both of the data tables to create your secondary data sets and the DATE mapping to give labels to those dates as well.

                     

                    I can write the query, but had attempted to join the tables in a single extract.   Independent sources makes a lot more sense.

                     

                    Like Chris, I had tried for a few days off and on and did not even come close.  Looking forward to seeing if I can make it work.  

                     

                    Thank you. 

                    • 7. Re: Year Over Year Two Ways - Assistance Requested
                      Joe Mako

                      Steve,

                       

                      The route I posted here is not ideal in all situations. I believe that even in this case it is better to have the join happen in the database. You are welcome to email me, found on my profile, and we can take a look at your situation over a screen sharing session and build a solution that fits your situation.

                      • 8. Re: Year Over Year Two Ways - Assistance Requested
                        chris.linderwell

                        Steve,

                         

                        This solution assumes you cannot make modifications to the database (add tables or manipulate data). Joe was kind enough to work through a more ideal solution with us that uses the assumption you can add tables (Lookup ones primarily) to SQL (or your source) so you’re not doing as much blending and the data comes in the right way.

                         

                        If it would be helpful we can post this solution for you (it’s rather robust but really scalable). We went through 4 hours of professional services support with no solution. This is truly a Joe Mako Zen solution. This new solution has become the best practice for us to create dashboards using a custom, fiscal calendar.

                         

                        We also have a recording that with Joe’s permission we can also provide that explains the work. It sounds like Joe is willing to review your problem with you so I’d understand if our solution was unnecessary based on his support.

                         

                        Thanks,

                         

                        Chris

                         

                        @Joe, thanks again for the aforementioned solution. We continue to be delighted by its implementation.

                        • 9. Re: Year Over Year Two Ways - Assistance Requested
                          Tim Sheridan

                          Chris,

                           

                          I am struggling with the same issue now.  Would it be possible to get a copy of that recording you made with Joe?

                           

                          Thank you,

                           

                          Tim

                          • 10. Re: Year Over Year Two Ways - Assistance Requested
                            Joe Mako

                            Tim, you are welcome to email me directly, found on my profile, and we can set a time to meet for a screen sharing session and walk through your situation together.