11 Replies Latest reply on Jun 16, 2014 8:33 AM by Noah Salvaterra

    Plotting a range bar chart

    Dylan Weaver

      I would like to plot a range for individual items on a bar chart in Tableau. I manually created picture of what I would like to accomplish below.



      Anyone have any ideas on a starting point? screenshot.png

        • 1. Re: Plotting a range bar chart
          Noah Salvaterra

          A few points of clarification:

           

          1. What is the x-axis?

          2. Is this a one time chart, or would it be connected to a dynamic source data (this isn't necessarily a problem, but some reshaping might be required and this helps guide my process)?

          3. Is there a reason ovals are used or would rectangles (i.e. Gantt bars) work just as well?

          4. Are the widths intentionally, or accidentally different? If it is intentional, what does it represent?

          5. What is color showing?

          6. Could you provide a sample of the data this would be based on?

           

          My first thought would be to do a full join with your data and a unit circle, then transform the base circle based on the data values, but I'd need more information to be sure that approach would make sense.

           

          N.

          • 2. Re: Plotting a range bar chart
            Dylan Weaver

            Thanks for your response!

             

            1. Water depth

            2. one time chart

            3. I would prefer bars, not really sure why I used ovals for the example.

            4. The width can be the count of the time (total volume)

            5. color is linked to company

            6. Data below:

             

            Rated Water DepthCompanyDesignCount
            300CPCP11
            400CPCP21
            300DD14
            400DD21
            375FF12
            350FF12
            400FF225
            350FF23
            400FF32
            400FF42
            300FF51
            350GG12
            400GG24
            375GG28
            350GG28
            220GG21
            400GG35
            375GG42
            450GG52
            492GG63
            460GG62
            492GG71
            574GG81
            400KK116
            350KK13
            262KK11
            400KK21
            300KK21
            500KK31
            400KK42
            400KK51
            400KK65
            400CC12
            375CC21
            350CC216
            340CC22
            200CC22
            400PP113
            400ZZ12

             

            Thanks for your help!

             

            Dylan W.

            • 3. Re: Plotting a range bar chart
              Noah Salvaterra

              1. Should have been, what is the y-Axis? Is it just discrete? i.e. records sorted by typical irking depth.

              6. Your data doesn't contain typical working depth. Is that a function of maximum?

                   Should each record be a bar, or should they be aggregated further?

              7. Is Design used anywhere in the viz?

              8. If this is a mockup, what sort of data source are you connecting to?


              I think it shouldn't be too hard, but will involve a bit of reshaping.

              • 4. Re: Plotting a range bar chart
                Dylan Weaver

                1. the Y-axis is volume (count of design). It should be discrete.

                6. No typical depth, the same designs can range in depth. I can deal with an aggregated view at the company level (then drill down further to the design level if I have to)

                7. Not yet

                8. I'm currently importing it from an excel table, eventually live connection to an Access database.

                 

                Thanks for you input.

                 

                Dylan W.

                • 5. Re: Plotting a range bar chart
                  Noah Salvaterra

                  Like this? Note, I'll walk you through what I did, just want to confirm it meets your needs first.

                  N.

                  1 of 1 people found this helpful
                  • 6. Re: Plotting a range bar chart
                    Dylan Weaver

                    This is amazing, great work! Yes, a walkthrough would be helpful.

                     

                    Kind Regards,

                     

                    Dylan W.

                    • 7. Re: Plotting a range bar chart
                      Noah Salvaterra

                      No problem. I've got a meeting in a couple minutes, so I'll lead with a high level version. I created 5 duplicate copies of the dataset. These are labeled via [Copy]. You could do this via a full unqualified outer join on a table with the numbers 1-5 in a field labeled Copy (I just did it in excel in this case). I'll help with this join later, it shouldn't be too bad (would be easier with just about any other database though)

                       

                      A copy is used to make each corner of the rectangles as polygons, so 4 copies, plus a 5th for labels which I used a dual-dual axis to create (though it occurs to me now one dual could have done the job just as well.

                       

                      Oh! Gotta go.

                       

                      N.

                      1 of 1 people found this helpful
                      • 8. Re: Plotting a range bar chart
                        Dylan Weaver

                        Thanks Noah, that is extremely helpful. I truly appreciate your time on this!

                         

                        Thanks again,

                         

                        Dylan W.

                        • 9. Re: Plotting a range bar chart
                          Noah Salvaterra

                          No problem, I'm happy to help when I can and how can I make this questions are among my favorites.

                           

                          I don't seem to have access on this machine. I was a bit surprised by this, but I guess I shouldn't be because I don't really use it very often anyway. I believe the same driver is used for Access as for text or excel files, so I tested a query with a couple text files. Let me know if this doesn't work for you and I can try on a different computer when I'm in the office tomorrow.

                           

                          You'll need to create a second table in your access database with just 1 column and 5 rows with the numbers 1 through 5. Actually any distinct values would do, but these numbers will correspond with the attached workbook so those will be simplest. Now, the JET driver won't want to let you do an outer join or an unqualified one (I believe) but there is a simple workaround to make it happen. Create a dummy field in each table (either directly or in a subquery as I did) and use that for your join criteria. I called my field dummy, again any value would do here, the point is that it be the same on every record in both table. It doesn't actually matter what type of join you do here, since every record is a match, but I did an inner join:

                           

                          SELECT A.[Company] AS [Company],

                            A.[Count] AS [Count],

                            A.[Design] AS [Design],

                            A.[Rated Water Depth] AS [Rated Water Depth],

                            B.[Copy] AS [Copy]

                          FROM

                          (SELECT [OperatingDepth#csv].[Company] AS [Company],

                            [OperatingDepth#csv].[Count] AS [Count],

                            [OperatingDepth#csv].[Design] AS [Design],

                            [OperatingDepth#csv].[Rated Water Depth] AS [Rated Water Depth],

                            1 AS DUMMY

                          FROM [OperatingDepth#csv]) as A

                          Inner Join

                          (SELECT [Copy#csv].[Copy] AS [Copy],

                            1 AS DUMMY

                          FROM [Copy#csv]) as B

                          ON A.DUMMY=B.DUMMY

                           

                          Note, this join looks more complicated then it really is. The two sub queries in blue are only there to add a dummy column with 1 for every record. Then these tables are combined so that you get a row for every match between the two tables, and that is every possible combination (i.e. a full cartesian product). So you get a copy of your original table for each number in the copy table.

                           

                          With these 5 copies, we are now in the case I built by hand for the initial prototype. We want to use 4 copies to build the rectangles and 1 for the labels.

                           

                          When I'm creating a polygon or line plot, I often make a quick sketch so I'm sure I get my calculations right. For a polygon, you just want to be sure you're going around the perimeter in order otherwise you can end up with weird bow-tie shapes. A rectangle was simple enough that I didn't find it necessary here, but it would be a good exercise to look at the calculations for X and Y coordinates and work through the picture. Note, the min around copy is a token aggregation, it was required because I used aggregation elsewhere in the calc but with [Copy] present on each marks card (path for the polygons and level of detail for the labels) the minimum value is just the same as the value itself. I multiplied by 1.0 in the X calculation because it was coming through as an integer value while the X(label) is a float, multiplying by 1.0 forces a type conversion so the axes could be synchronized.

                           

                          I just did a standard dual axis in this version (the dual-dual being un-necessarily complex as I mentioned before). The case statement will be null where not defined do X only exists for copies 1-4 and X(label) only exists for copy 5. Y can do double duty, so there is no need to use a dual axis for this. If the indicator that there are null values shows up, you can hide it, these nulls are there for a reason.

                           

                          Please let me know if you have any problems or if there is anything I left out of my explanation that you don't understand.

                           

                          N.

                          • 10. Re: Plotting a range bar chart
                            Dylan Weaver

                            Noah,

                             

                            I can't thank you enough for the time and effort you put into this. Your explanation and examples have been a great help. I'll play with it a little more as I recreate it. If I have any quesitons I'll post them up.

                             

                            I've never recieved this type of help from a forum before. It's nice to know there are great people out there.

                             

                            Thanks again!

                             

                            Kind Regards,

                             

                            Dylan W.    

                            • 11. Re: Plotting a range bar chart
                              Noah Salvaterra

                              No problem Dylan. I'm glad I could help. Feel free to ping me if you get stuck on anything.

                              N.