6 Replies Latest reply on Dec 17, 2012 7:35 PM by brian.comeau

    Holidays

    brian.comeau

      Hi Tableau Community,

       

      This is my first post and potentially very basic, so please bear with me.

       

      My situation:

       

      • I have two tables of data 1. Transactions by Day, 2. Holidays
        1. DATE | Description | Quantity
        2. TYPE | Description | DATE FROM | DATE TO
      • I would like to be able to map my transactions per day onto a visualization as an Area or bar chart.
      • I would then like to be able to color the sections of 1.DATE that fall between the 2.DATE FROM and 2.DATE TO
      • These colors could be by 2.TYPE if I dragged that to 'Colours'

       

      UPDATE: I also don't know how to link the DATE from 1. to the two date fields from 2.

       

      I hope this makes sense. I'm basically trying to visualize data to tease out any patterns during holidays or particular holiday types.

       

      Thanks,

      Brian

        • 1. Re: Holidays
          Alex Kerin

          Can you post a sample tbwx that has the two files connected? If the data is sensitive, mock up the data (http://community.tableau.com/thread/121116)

          • 2. Re: Holidays
            brian.comeau

            Hi Alex Kerin,

             

            I've attached the file as requested. This is just the SuperStore data and the holiday file I'm using. Ship Date or Order Date could be mapped to that I suppose.

             

            Thanks,

            Brian

            • 3. Re: Holidays
              Alex Kerin

              Perfect tbwx - thanks.

               

              This can best be achieved with a join. I created an excel file with the sales data on one sheet and your dates on another. They could also have been two text files in the same directory or two tables in a database (but not a mix)

               

              I connected to the sales sheet, then chose multiple tables, add table, chose the Dates sheet and defined my joins. I deleted the default join and added [Order Date]>=[From Date] and another for [Order Date]<=[To Date].

               

              Chose left join - the sales data is the left side - we want to return all sales data even if it isn't matched to a holiday period. If you leave it as inner, only Order Dates that are a holiday will be returned. You can continue adding Joins as needed - for example a country=country if needed.

               

              Some caveats. If there are overlapping holidays, you will get duplicate sales data rows back - all of the rows that match both holidays will be duplicated. This occurs in this data set - the sales total should be around $14m. Because you have some overlapping holidays, the total in this joined file is $17m. There's not a straightforward way of getting around this that comes to me - if you pad the data (so that every day of a holiday has a date associated with it) and then blend rather than join you still run into difficulties..

              • 4. Re: Holidays
                brian.comeau

                Thanks Alex Kerin, this helped!

                • 5. Re: Holidays
                  Alex Kerin

                  If you were plotting the data out and the [sales] was already aggregated to the day, you could do a min([Sales]) otherwise you'd have to resort to table calculations to get the right sales sum.

                  • 6. Re: Holidays
                    brian.comeau

                    Hi Alex,

                     

                    I've used % of Total -> First() which is 'Null', which is the non-holidays. I've used the Average Sales, so I get a % difference from the normal day's average to see the impact of holidays.

                     

                    I think this works and is what you meant.

                     

                    Thanks,

                    Brian