1 2 Previous Next 18 Replies Latest reply on Dec 24, 2013 3:33 PM by Jonathan Drummey

    Compare a Given Month over different Years

    eli.goldner

      Hi,

       

      I have a table which has data as in the table below (dummy data). Sales data is recorded by item, and by sales period (YYYYMM). The sales period is stored as a date data type.

      I want to create a graph which will show how an item performed in a given month over several years by month as compared to the latest period which we have. Case in point (latest period which I have at the moment is 201310), I would want a graph to have two points for the pencils as follows:

      1. pencils for 201310
      2. pencils for 201210

       

      I tried to get the MONTH(sales period), abn

      Itemsales periodsales
      pencils201310$123
      pens201310$458
      pencils201309$133
      pens201309$475
      pencils201308$122
      pens201308$444
      pencils201210$112
      pens201210$485
        • 1. Re: Compare a Given Month over different Years
          bill.cunningham

          Hi Eli,

           

          Check out the attached workbook to see if this is what you had in mind.  I created two calculated fields based on 'sales period', one field that just pulls the 4 left-most characters to determine the year, and another field that pulls the 2 right-most characters to determine the month.  Filtering on a month will compare all available years side by side for the selected product.

           

          Hope this helps!

          Bill

          • 2. Re: Compare a Given Month over different Years
            eli.goldner

            Bill,

             

            I opened your worksheet, and it is showing what I was looking for, though it is in numeric form, instead of dates... will play around with this and let you know how it goes.

             

            Eli

            • 3. Re: Compare a Given Month over different Years
              bill.cunningham

              Try just adding another calculated field using this formula to generate an actual date using the 1st calendar day of each month:

               

              date(str([sales period (month)])+'/1/'+str([sales period (year)]))

              • 5. Re: Re: Compare a Given Month over different Years
                Matt Lutton

                Is this what you need (see attached 8.0 workbook)?

                Untitled.png

                I used the calculation Bill C suggested above, and replaced your Year and Month pills with the newly calculated date pill in Month-Year format.  Let me know if this makes sense.

                • 6. Re: Re: Compare a Given Month over different Years
                  eli.goldner

                  Matthew,

                   

                  Thanks for your response. I like one thing from your example (using dates, correct filter; though in our example the user needs to select a month to compare) , it still isn't what I am looking for...

                   

                  Here's a clarification :

                  I have two items which are being sold: pens and pencils

                  I want to have two graphs on a dashboard showing different metrics:

                  1. a running graph of the sales by month

                  2. a running graph of the sales of the latest month, in a year-over-year analysis.

                   

                  I want the month for the second graph to be calculated based on the latest month contained in the data for the given product. I have, in production used a second data source to filter this out on the SQL level (my comfort zone) and I'm paying the price now for a cheap solution...

                   

                  I will create a viz with the dummy information (cannot have real information due to privacy) for you to play with, as I see that a visualization will be the only way to really explain what I am referring to. I will try to have it up later today.

                   

                  Thanks,

                   

                  Eli

                  • 7. Re: Re: Re: Compare a Given Month over different Years
                    eli.goldner

                    Matthew/Bill,

                     

                    Please see the attached workbook with the same data as seen in the original question.

                     

                    I set the item name as a filter for all sections of the dashboard, though I want the upper graph (labeled year over year) to update based on the latest month for which we have data... if the items have different months, then the graph should lay itself out differently based on the user selection... I don't want the user to have to select a which month to display.

                     

                    there is one difference between this viz and the one I am using in production: in production I am using a second data source to get the data for the year over year page, though here I left it in one data source for you to attempt to get it to work properly.

                     

                    Any help is greatly appreciated!

                     

                    Eli

                    • 8. Re: Compare a Given Month over different Years
                      Jonathan Drummey

                      Hi Eli,

                       

                      I have a couple of questions re: #2:


                      - Do you mean a graph filtered for the latest month, with the Year on columns, where every mark corresponds to the the appropriate year & latest month for that product?

                       

                      - Do you want that graph to be a running total of the YoY sales for each product, or just the product's sales for that year?

                       

                      - You noted that the latest month can be different for different products. That leads to a question whether there will always be data for past months for every product? In other words, is is possible that a product could have Sales in December 2013 but no sales (and therefore no records in the data) for December 2012?

                       

                      Also, what's your data source and volumes? And what were you using for the SQL solution you're trying to get away from? I'm asking because getting that latest month inside Tableau generally either requires a table calc or a data blend, and for various reasons both can be computationally expensive. I've found doing the work in SQL can be faster.

                       

                      Jonathan

                      • 9. Re: Compare a Given Month over different Years
                        eli.goldner

                        Jonathan Drummey wrote:

                         

                        Hi Eli,

                         

                        I have a couple of questions re: #2:


                        1- Do you mean a graph filtered for the latest month, with the Year on columns, where every mark corresponds to the the appropriate year & latest month for that product?

                         

                        2- Do you want that graph to be a running total of the YoY sales for each product, or just the product's sales for that year?

                         

                        3- You noted that the latest month can be different for different products. That leads to a question whether there will always be data for past months for every product? In other words, is is possible that a product could have Sales in December 2013 but no sales (and therefore no records in the data) for December 2012?

                         

                        4 - Also, what's your data source and volumes? And what were you using for the SQL solution you're trying to get away from? I'm asking because getting that latest month inside Tableau generally either requires a table calc or a data blend, and for various reasons both can be computationally expensive. I've found doing the work in SQL can be faster.

                         

                            

                         

                        Jonathan

                         

                        Jonathan,

                         

                        Thanks for your quick reply!

                        I labeled your points above, and responded accordingly below.

                         

                        1. Not exactly, I want the graph filtered for the latest Month across several years. Example: the latest sales period in the sample data is 10/2013. I would want to see data for 10/2011, 10/2012 and 10/2013. Forgive me if I wasn't clear.
                        2. The latter
                        3. There will be data most of the time... the only exception would be a new product for which we don't have sales figures for the previous years... there will not be any gaps in data for pre-existing products, The reason why I left gaps in the example was in order not to have the need to create values for each month.
                        4. data source/volumes/ what's wrong with using two data sources as a SQL Solution to the problem
                          1. Data source: SQL Server
                          2. Volumes: we have data for decades... I'd like to import as little data as possible into Tableau... what I currently have in the real example is 13 months for the lower graph and three years of data (just of the relevant months) for the upper (or second) graph.
                          3. I plan on having 13 months of sales per item (figure about 10-15 items) plus the old years where needed.
                          4. The current solution has two data sources which get the correct data (first pro), and only import the requisite data (second pro). The issue which I am facing is as follows: since I have two data sources, and I would like to have a quick filter on the dashboard in order to display only one product at at time... I would need to have the two pages on the dashboard be from the same data source. If you have an alternative solution to this problem, I would be glad to hear you out!
                        • 10. Re: Re: Compare a Given Month over different Years
                          Jonathan Drummey

                          Thanks for the answers..., I have a couple more questions.

                           

                          5) Are you creating a Tableau data extract or a live connection running queries against SQL Server? Because you said, "import as little data into as possible into Tableau", I'm wondering whether you are trying to create small extracts or something else? Because if you have a properly tuned SQL Server, a couple of live queries that have a relative date filters for only the last three years*** or thirteen months should be fine, so you might not need two separate data sources.

                           

                          *** The harder part here is flagging the latest month for each product. If you're doing a live connection to your SQL Server then there are a couple of options. One would be to have a materialized view that returns the latest month for each product to each row, the other is a table calc solution. Given that you are only querying one product at a time, the table calc solution should be doable, you'd be grabbing up to 36 rows of data (one for each month per product).

                           

                          Depending on the answer to 5), you might be able to get away with one data source, and that would make the filter across data sources problem go away. If you have to have two data sources, there are a couple of routes to filtering across them, one is to use a Tableau Parameter, the other is to use a Filter Action. I set up both in the attached, using Superstore Sales as an example.

                          • 11. Re: Re: Compare a Given Month over different Years
                            eli.goldner

                            Jonathan,

                             

                            We currently have version 8.0.2 running and it won't let me open your workbook due to yours having been created in a newer version.

                             

                            Thanks,

                             

                            Eli

                            • 12. Re: Re: Re: Compare a Given Month over different Years
                              Jonathan Drummey

                              Hi Eli,

                               

                              Here's an 8.0 version, my apologies for the delay!

                              • 13. Re: Re: Re: Compare a Given Month over different Years
                                eli.goldner

                                That's ok... will look at this later today.

                                 

                                Thanks,

                                 

                                Eli

                                • 14. Re: Compare a Given Month over different Years
                                  eli.goldner

                                  Jonathan,

                                   

                                  I reviewed it.

                                  The data is slightly off from what I was aiming at, though that doesn't distort the issue at hand at all; I will therefore not address the differences in this email.

                                  I was able to recreate the filter action sheet, to me it seemed as it would be the simpler approach (I was also able to find another tableau post which showed me how to get rid of the "abc" column in the filter action sheet).

                                   

                                  One more question: there is one feature which is available in the parameter approach, though seemingly unavailable in the filter action approach... and that is the ability to prevent the user from selecting all and having the graphs distorted by all of the different color lines rendering the graph unreadable. Is there a way to do this in the filter approach?

                                   

                                  Thanks!

                                   

                                  Eli

                                  1 2 Previous Next