13 Replies Latest reply on May 30, 2012 5:31 PM by Jonathan Drummey

    How do I create a scatter plot using X and Y data from different tabs?

    Chris Ralph

      I'm new to Tableau so maybe there's an easy answer to this, but I can't for the life of me figure it out.  I have a worksheet with two tabs, each of which contains 7 values that I'd like to plot together as the X and Y values in a scatter plot.  How would I do this?!

       

      Many thanks in advance!

      Chris

        • 1. Re: How do I create a scatter plot using X and Y data from different tabs?
          Alex Kerin

          Are the values from the same source? What are the calculations creating the values?

           

          And welcome to the forums...

          • 2. Re: How do I create a scatter plot using X and Y data from different tabs?
            Chris Ralph

            The underlying data is from the same data source, and the values from from calculated variables containing other nested, conditional calculated variables as inputs.  I could create a tab with all 14 values together in a data table, but still don't know how to specify the X and Y values for the scatter plot. 

             

            And thanks very much for responding so quickly Alex!  Any help you can provide would be fantastic.

            Chris

            • 3. Re: How do I create a scatter plot using X and Y data from different tabs?
              Alex Kerin

              Can you post a tbwx (tableau file with the data included). If the data is sensitive, perhaps you could mock-up/anonymize it?

              • 4. Re: How do I create a scatter plot using X and Y data from different tabs?
                Chris Ralph

                Hi Alex,

                I wish I could, but I can't share this particular data and anonymizing it would be very difficult because the calculated variables I'm trying to plot are complex because they're nested (i.e. multiple levels of other calculated variables make up the inputs to the ones I'm trying to plot).  Maybe this description will help a little:

                    

                I'm using a single data source, and creating 14 separate calculated variables, half of which represent values on my "X" axis, and the other half represent values on the "Y" axis.  The 7 'X' values use a common scale, and the
                'Y' values use another common scale, and I just want to plot them against each other in a scatter plot. 

                 

                In my worksheet I have two tabs.. the first displays the aggregated 'X' values as Measure Values as percentages, and the second shows average 'Y' values as Measure Values displayed as basis points (hundredths of a percent).  The ordered values in those tabs can be put together to form X-Y pairs that will define the points on the scatter plot.

                 

                I've been looking everywhere but can't figure out how to manipulate these calculated variables in a way that allows me to plot them.. again, thanks for your help!

                 

                Chris

                • 5. Re: How do I create a scatter plot using X and Y data from different tabs?
                  Alex Kerin

                  Difficult then - if this is a once off, or rarely repeated I may think about copying the summary data out to Excel and bringing it back in.

                  • 6. Re: How do I create a scatter plot using X and Y data from different tabs?
                    Chris Ralph

                    Ugh. 

                     

                    Going out to Excel & back isn't an option for me because of the intended audience for this particular tool.  Your comment that this is "difficult" implies that it's "possible".. the degree of difficulty doesn't bother me if there's a way to do it in Tableau.. there must be some Tableau Black-Belts out there willing to take on this "simple" problem of plotting sets of calculated variables from different tabs in a worksheet with a common data source.. any other takers?

                     

                    Again, thanks very much in advance for any help!!

                    Chris

                    • 7. Re: How do I create a scatter plot using X and Y data from different tabs?
                      Jonathan Drummey

                      Hi Chris,

                       

                      Assuming that you can control where the 14 results go in the worksheet, I imagine the easiest solution might be to create a third worksheet in Excel that has three columns: an ID (unique to each X/Y pair), the X-values, and the Y-values, where the X and Y values are just functions like =X!A2, =X!A3, etc. that point to the original worksheets. Then you can have that worksheet be the datasource in Tableau.

                       

                      A second solution is to set up the ID and have two columns in your X worksheet, one for the ID and one for the X values, and two columns in your Y worksheet, one for the ID and one for the Y values. Then when you create the datasource in Tableau you can do use the Multiple Tables option and left-join the Y values to the X values on the ID field.

                       

                      Either way, Tableau ends up with 7 rows each with an ID, an X value, and a Y value. Then you can put X on Columns, Y on Rows, and ID on the Level of Detail Shelf to generate your scatterplot.

                       

                      I've set up both in the attached workbook.

                       

                      Jonathan

                      • 8. Re: How do I create a scatter plot using X and Y data from different tabs?
                        Chris Ralph

                        Thanks very much for the response Jonathan!

                         

                        The first approach you suggest (setting up a 3rd worksheet in Excel) isn't feasible because the plots must be dynamic & change when the X-Y pair values change, which is a function of other parameters in my worksheet.  The Excel method requires that the data be captured, manipulated manually, and read back in every time a value changes.

                         

                        Your second solution sounds more promising to me, but I have to figure out how to set up the ID's using a single datasource (12,000 rows), where the X-Y values I want to plot are aggregated calculated variables.  What if I created an ID variable that is randomly created across the 12,000 rows (so that the X-Y pair names are represented as a Dimension), and then created two conditional calculated aggregate variables for the X values and Y values.  If the X & Y calculated variables are constant across the conditional rows in my data (i.e. rows with ID#1 have the X&Y values for scenario #1, rows with ID#2 have the X&Y values for scenario #2, etc.) I think I can trick Tableau into generating the scatter plot I want.

                         

                        Does that make sense? 

                        Chris

                        • 9. Re: How do I create a scatter plot using X and Y data from different tabs?
                          Jonathan Drummey

                          Hi Chris,

                           

                          I'm still not understanding why the first approach (let's call it "third worksheet") won't work, assuming that you can algorithmically identify the location in the workbook of the X and Y values that need to be returned. Here's the way I see it:

                           

                          1. Open up the Excel file.

                          2. Change parameters/calculations in the data. As soon as you do that, the results for the third worksheet would also change.

                          3. Save the Excel file.

                          4. Open the Tableau workbook, or if it's already open press F5 to refresh. Tableau uses the updated results. If the workbook is being published to Tableau Server, then you can make it the Excel data source a live connection and as soon as step 3 is complete then the updated data is available.

                           

                          No matter what, given an Excel data source, for Tableau to plot different data someone or some application is going to have to do steps 1-3. So I don't see how the first option would be extra manual effort than is already necessary.

                           

                          However, your second paragraph maybe provides some explanation. Given that the 12,000 rows in the data go through some number of transformations and aggregations to end up with the 14 numbers, it seems like where the numbers come from might be changing based on the parameters and/or data? In that case, the solution you outlined seems like it will work. (Alternatively you could probably still create a version of the "third worksheet" solution using VLOOKUP/HLOOKUP).

                           

                          Cheers,

                           

                          Jonathan

                          • 10. Re: How do I create a scatter plot using X and Y data from different tabs?
                            Jonathan Drummey

                            Hi Chris,

                             

                            I just re-read what I wrote and read further back, and realized that I've been assuming Excel because you started talking about worksheets and you might have been talking about Tableau all along. Are you talking about having two Tableau worksheets that create the different numbers based on the 12,000 rows in your data? If so, then your second approach is the way to go.

                             

                            If it involves table calculations, then check out Richard Leeke's post on reducing the number of rows returned: http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html. With most table calculations you can get them to return one value per combination of dimensions and therefore end up plotting just the 7 points that you need.

                             

                            Jonathan

                             

                            • 11. Re: How do I create a scatter plot using X and Y data from different tabs?
                              Chris Ralph

                              Ahh.. sorry for the confusion!  You're correct that I'm doing everything in Tableau worksheets, not Excel.  I'll read through Richard's post, and let you know how it goes.

                               

                              Thanks again!!

                              Chris

                              • 12. Re: How do I create a scatter plot using X and Y data from different tabs?
                                Chris Ralph

                                (sigh)

                                 

                                I'm almost there with creating the X-Y scatter plot for my 7 value pairs taken from different worksheets.  To re-iterate, I'm trying to use calculated variables from two different tabs (one for the X's, and one for the Y's) in my Tableau worksheet to create a scatter plot (X vs. Y).  My challenge has been trying to figure out how to create a data structure that Tableau expects without having to resort to a manual "export to Excel & import back to Tableau" approach.  I've learned a lot along the way, but I'm not quite there yet so anyone who reads this & has an idea please let me know!!  Here's a description of my progress so far:

                                 

                                Building on Johnathan's idea of using an ID variable to define the X-Y pairs, I created a calculated variable called "Scenario" which randomly sets a value for each row in my Tableau data table as a string (i.e. "Scenario 1", "Scenario 2", etc.).  To do the random assignments I had to use the last digit of a 'Postal Code' field, which in itself was frustrating.. a native random number generator would be a huge help for certain types of visualization or data processing tasks in calculated variables like this one (.. how do I know if this is on the list of enhancements?).  Anyhoo, once I had the ID labels for each XY pair I just picked my calculated variables as the measures and create the plot. 

                                 

                                The problem is that each calculated variable is only calculated using the rows for the scenario that row is assigned to.  I thought the "random" assignment would help ensure that the average values within a scenario-group of rows is representative of the overall population, but no luck.. the XY pairs are directionally correct, but the randomness added some enough to the values to make them un-usable. 

                                 

                                So, what I think I need to do now is somehow "push" the calculated variables values into a calculated column in my Tableau table that is constant across all the rows.  If I could do that, then the 'ID'/Scenario structre could be leveraged to create the scatter plot, and the value pairs would be equal to the overall population values. 

                                 

                                Before responding, please keep in mind that I want all of this to be done dynamically within Tableau.. writing out & reading in temporary tables manually isn't an option for me.

                                 

                                Of course, I'm open to other suggestions as well.. maybe there's a way to manipulate the structure of crosstabs in Tableau?  Any further ideas would be greatly appreciated!

                                 

                                Thx!

                                Chris

                                • 13. Re: How do I create a scatter plot using X and Y data from different tabs?
                                  Jonathan Drummey

                                  Hi Chris,

                                   

                                  Did you ever get this figured out? There are ways to "push" values into another column, but at this point I think I'd need to see a packaged workbook with some sample data to give you a suggestion.

                                   

                                  Jonathan