8 Replies Latest reply on Jun 28, 2013 6:49 AM by Jie Hao

    Combine different data source and apply dual axis

    Jie Hao

      Before I can use excel to draw a nice graph where I can combine two different data source and apply two different measurements into one graph. To be more specific, the two bars (total order value & total order value Prob) are from one source, the line (visits per week) is from another source. I tried to use tableau to populate same graph, but the visits per week is not showing as a line, instead it's showing dots (even I chose to mark type as line ad continous). Is there a way to make the same graph as excel? I attached two documents (excel & tableau) for your reference. Thanks.

        • 1. Re: Combine different data source and apply dual axis
          Mark Holtz

          Hello Jie,

           

          Hard to tell exactly without fiddling with the workbook itself, but I think Tableau is making them separated points  because you have discrete dimension pills on the columns shelf--the Site field and the special Measure Names field. Even if you take Measure Names out (you have it in the colors shelf, so it should still separate the measures, although the bars might turn into a stacked version. Maybe you can unstack and offset them.

           

          But you might still have the issue in that Site is not continuous, so I'm not sure Tableau will draw a line across discrete dimension buckets. But, I believe there is a setting to connect points. 

           

          If that doesn't get it where you want it, can you post your packaged workbook?

          • 2. Re: Combine different data source and apply dual axis
            Jie Hao

            Hi Mark,

             

            I'm combining 3 different data source in this case. Where the avg (visits per week) is calculated using the data from "details customer visit" and "working weeks", so that's something I want to draw a line. The total value is from "sales pipeline" (two bars). I just wonder excel can manage this task easily, why tableau can't. Or Tableau can but I haven't figured out how .

            This time I attach the packaged workbook.

             

            Thanks.

            • 3. Re: Re: Combine different data source and apply dual axis
              Mark Holtz

              Jie,

               

              To my knowledge, the Excel screenshot you showed is not available in Tableau. You're right it would be a good feature. There are a few "headsmackers" still out there that are so simple to do in Excel that either require incredibly complex workarounds or are just plain impossible in Tableau.

              There is an idea posted to add the functionality you desire, which the Tableau Product Development team uses to get customer feedback and wants for future releases. You can up-vote the idea here: http://community.tableau.com/ideas/1487

               

              I provided your workbook back with a couple options that try to simulate what you want, but I realize they are not exactly what you asked for. Again, I am not sure that's possible in Tableau as of yet...

               

              You might find better/other ideas on Jonathan Drummey's blog: http://drawingwithnumbers.artisart.org/wiki/tags/bar-charts/

              He has shared some very inspired insights on tricks and workarounds in Tableau.

               

              Hope something here is helpful to you.

              1 of 1 people found this helpful
              • 4. Re: Re: Combine different data source and apply dual axis
                Jie Hao

                Thank you very much Mark.

                 

                Yes, it's just a bit pity that something can be so easily done by excel, but not in Tableau. Hope in future this will be possible.

                • 5. Re: Combine different data source and apply dual axis
                  Jonathan Drummey

                  The reason why it's not possible out of the box in Tableau is that you have two discrete (blue) pills on Columns. Tableau will connect lines across one discrete, but not two. The reason why there are two discretes is that the second discrete is the Measure Names convenience dimension.

                   

                  In a case like this, Measure Names is being used because the data is structured that way, so reshaping the data to be more suitable for Tableau can get you what you want. Here's how I'd go about it, and this is one of those "incredibly complex workarounds" that Mark talked about.

                   

                  1) Reshape the data so that those columns for the bar measure are a single column, with a new column indicating the measure. I do this with a UNION query. This gets rid of the need for Measure Names.

                  2) Bring that in as a new source.

                  3) Create a continuous value that corresponds to each country, like 1-N. You could do this as an extra column in step 1 or via the INDEX() table calc. The reason why we need the continuous value is because we need an axis to plot the measures on.

                  3) Build a grouped bar chart using that continuous value and your new dimension & columns. Links to how to do that are in what Mark posted, it requires some calculated fields and some fiddling to get things to line up right. Include the country on the level of detail.

                  4) Add the measure from the secondary dimension. This should go very smoothly.

                  5) Turn off the continuous axis label (the 1-N).

                  6) Create another worksheet that has just the country headers.

                  7) Create a dashboard with a vertical layout container, then put your two worksheets in the container.

                  8) Fiddle with the spacing/fit settings to get the two worksheets to line up.

                  9) For further refinement, you can do things like add a Highlight Action between the worksheets, disable the filtering tooltip (because only one worksheet would get filtered), etc.

                   

                  Jonathan

                   

                  [edited 20130627 - had pressed submit too soon]

                  1 of 1 people found this helpful
                  • 6. Re: Combine different data source and apply dual axis
                    Jie Hao

                    Hi Jonathan,

                     

                    Thanks so much for your detailed steps, but I'm not sure if I can follow. I guess I will just rely on excel in this case untill tableau features can be improved in future.

                     

                    Thank you guys.

                    • 7. Re: Re: Combine different data source and apply dual axis
                      Jonathan Drummey

                      I had a little time this morning so put together an actual example for you, see the attached. There were a couple of extra bits I needed to do. One was to add an additional UNION to be able to plot the line chart on the center of each column, the other was the table calc for the INDEX() requires the new column to be in the view, and uses an Advanced... Compute Using. If your data source could have a unique # for each state, you wouldn't need that bit of complexity. It took about 20 minutes from start to finish.

                       

                      Jonathan

                      1 of 1 people found this helpful
                      • 8. Re: Combine different data source and apply dual axis
                        Jie Hao

                        wow....I will study on it...

                         

                        Thanks a lot for your time!