5 Replies Latest reply on Aug 24, 2009 10:48 PM by Michael Mixon

    Trying to join two Excel worksheets into a single view

    Michael Mixon

      I tried looking for a post that covered this, but couldn't find one that directly addressed my issue (which is pretty basic).

       

      I have two Excel worksheets -- they happen to be from the same workbook, but that won't always be the case.  One contains shipment actuals, with granularity down to the week, customer and product level.  The other worksheet contains quarterly targets by customer and product segment.  What I'd like to do is join these two data sets into one, so that I can have an actuals vs target graph on a single Tableau worksheet. 

       

      Even though I join the two tables on the common dimensions (e.g. customer name, product segment), the target values get wildly inflated when I bring them into Tableau.  My guess is that it has to do with the different grain between the actuals and targets worksheets (product/week vs. product segment/quarter).  Would I need to aggregate my actuals to the same level (e.g. product segment and quarter) in order for that join to work?  Or are there other options?

       

      Thanks.

       

      -Mike

        • 1. Re: Trying to join two Excel worksheets into a single view
          Joe Mako

          Mike,

           

          From your description, it seems like you want to pre-aggregate one data set before connecting and join it with the other. I can see a few routes to accomplish this.

           

          One possible route is to create the following function to create your Shipped Actual:

           

          IF DATEPART('year',[Date]) = [Year] AND "Q"+DATENAME('quarter',[Date]) = [Quarter] THEN
          
           [Shipped]
          ELSE
          0
          END
          


           

          If you have just a week number, the condition would be different. You could also put this into an IIF function instead of an IF.

           

          The limitation of this route is that the quarterly targets cannot be summed together.

           

          Take a look at the attached source file and packaged workbook for what I was able to gather from your discription and using this first route.

           

          Are you able to preprocess your data?

           

          Is the data source I created similar structure you what you are working with?

           

          Does this get you to the point where you can answer your questions?

           

           

          As for joining two different Excel files together, Tableau's SQL can only pull from one data source entity to be the data behind a single worksheet. If you need your actual and target graphed on the same sheet, they will need to be in the same data source or Excel file.

          • 2. Re: Trying to join two Excel worksheets into a single view
            Michael Mixon

            Hi Joe,

             

            Thanks for the feedback.  I think I'm still doing something wrong -- your example essentially captured my situation, but your file works whereas I am still getting inflated numbers.  I'm attaching a sample with my data.  As you will see, the Actuals are fine when I do the join, but the Targets are WAY high.  I am not sure how I would use your Shipped Actual calculated field on my data, as I don't have calendar dates in my actuals.

             

            I'm sure there is a simple solution for this, but I'm just not seeing it. :-)

             

            Looking forward to your response.

             

            -Mike

            • 3. Re: Trying to join two Excel worksheets into a single view
              Joe Mako

              Mike,

               

              Thank you for the additional details.

               

              After seeing your data structure, there is no need for my "Shipment Actual" calculated field above.

               

              Seeing that you want to filter your field "Fiscal Week Age" to 0 and below, you may find use in this calculated field:

               

              Final Gross:

               

              IIF([Fiscal Week Age] <= 0,[Final Gross Fcst Qty],0)


               

              Using this calculated field instead of placing "Fiscal Week Age" on the Filters shelf, will cause all values in "Cust Group Desc" to be displayed.

               

              Also because you have NULL values in some of your fields, I would also recommend wrapping them like so:

               

              Final Gross:

               

              IIF([Fiscal Week Age] <= 0,IIF(ISNULL([Final Gross Fcst Qty]),0,[Final Gross Fcst Qty]),0)


               

              Target:

               

              IIF(ISNULL([Total]),0,[Total])


               

               

              Because you have "Fiscal Qtr Year" in your "Actuals" data set and "Qtr" in your "Targets" data set, you will want your Join to look like the attached image "Join.png".

               

              Here is an example of what is happening:

               

              Actuals data set:

               

              Cust | Qtr | Value
              
               Joey | Q-1 | 10
              Joey | Q-1 | 20
              Joey | Q-1 | 30
              Joey | Q-2 | 40
              Joey | Q-2 | 50
              Joey | Q-2 | 60
              Dave | Q-1 | 100
              Dave | Q-1 | 200
              Dave | Q-1 | 300
              Dave | Q-2 | 400
              Dave | Q-2 | 500
              Dave | Q-2 | 600
              


               

              Targets data set:

               

              Cust | Qtr | Target
              
               Joey | Q-1 | 50
              Joey | Q-2 | 75
              Dave | Q-1 | 500
              Dave | Q-2 | 1000
              


               

              After the Join:

               

              Cust | Qtr | Value | Target
              
               Joey | Q-1 | 10 | 50
              Joey | Q-1 | 20 | 50
              Joey | Q-1 | 30 | 50
              Joey | Q-2 | 40 | 75
              Joey | Q-2 | 50 | 75
              Joey | Q-2 | 60 | 75
              Dave | Q-1 | 100 | 500
              Dave | Q-1 | 200 | 500
              Dave | Q-1 | 300 | 500
              Dave | Q-2 | 400 | 1000
              Dave | Q-2 | 500 | 1000
              Dave | Q-2 | 600 | 1000
              


               

              This causes the target to be repeated in each row. You can make a valid comparison, by comparing "Sum of Value" to "Average of Target".

               

              After aggregation:

               

              Cust | Qtr | Sum of Value | Avg of Target
              
               Joey | Q-1 | 60 | 50
              Joey | Q-2 | 150 | 75
              Dave | Q-1 | 600 | 500
              Dave | Q-2 | 1500 | 1000
              


               

              If you want to aggregate at any other level, I would recommend preprocessing the Actuals data set so it is a one-to-one join instead of a one-to-many.

               

              There are a few options, one is running sums, see "Running Sum Table" tab. You can edit the table calculations so the Running within option is at the level you want to see, and just look at the last value in that set.

               

              Other than that, there are quite a few ways to visualize this data; I created a couple of visualizations from your data set.

               

              Viz 1 & 2, take all the week ages into account, and Viz 3 & 4 only include data from week age 0 and below.

               

              From the example sheet you created, it seemed like you wanted to compare the Actual vs Target, filtered to week age 0 and below, and aggregated to the Market Segment level. Viz 4 is my attempt to accomplish that goal.

               

              Do any of these approaches help you answer your questions?

              • 4. Re: Trying to join two Excel worksheets into a single view
                Joe Mako

                Thought it might be useful to include the steps to create Viz 4.

                 

                1. Calculated fields:

                Final Gross:

                 

                IIF([Fiscal Week Age] <= 0,IIF(ISNULL([Final Gross Fcst Qty]),0,[Final Gross Fcst Qty]),0)


                 

                Target:

                 

                IIF(ISNULL([Total]),0,[Total])


                 

                Difference:

                 

                SUM([Final Gross])-AVG([Target])


                 

                Pane:

                 

                "Pane"

                   

                  2. Place "Measure Names" on the Filters shelf, select only:

                  Difference

                  Sum of Final Gross

                  Sum of Target

                   

                   

                  3. Place "Market Segment", "Measure Names", and "Pane" on the Columns shelf

                   

                  4. Place "Measure Values" on the Rows shelf

                   

                  5. In the Measure Values shelf, change "SUM(Target)" to "AVG(Target)"

                   

                  6. Place "Cust Group Desc" on the Level of Detail shelf

                   

                  7. Place "Measure Names" on the Color shelf, setting colors as needed

                   

                  8. Adjust the Sort of "Measure Names" as desired, I felt Target, Gross, Difference was a good sort

                   

                  9. Add a Reference Line for Computed Sum with Value as the Labels, format the Reference Line so the Line is None

                   

                  10. Uncheck Show Headder on the "Pane" pill on the Rows shelf

                   

                  11. From the main menu, select Format -> Borders..., and move the slider one tick to the left for the botton one, Column Divider Level

                   

                  Likely not the simplest process, but I feel in this case it works.

                  • 5. Re: Trying to join two Excel worksheets into a single view
                    Michael Mixon

                    Thank you Joe...you helped me figure it out.  Using Avg vs. Sum was the major 'a-ha' I needed.

                     

                    Instead of the Running Sum approach for the Segment totals -- I'm still not clear why I am unable to just sum the averages -- I decided to break out the segment totals onto their own sheet and then do a 3-way table join.  Works perfectly.