1 2 Previous Next 17 Replies Latest reply on Feb 25, 2019 3:46 PM by Naveetha Donthi-Reddy

    Two Bars and Two Lines in one chart.

    Naveetha Donthi-Reddy

      Hi,

       

      I'm trying to plot two bars and two lines in one chart. Attached package workbook has required data in excel, I know that we can write SQL query to achieve it. But my source is SharePoint  List and I don't think I can write a SQL query as we can do with other data sources.

       

      Your time is highly appreciated. TIA!

       

      -Naveetha.

        • 1. Re: Two Bars and Two Lines in one chart.
          Zach Leber

          One option is to use two views on one dashboard as shown below and attached in TWBX.

          two bars and lines.png

          • 2. Re: Two Bars and Two Lines in one chart.
            Zach Leber

            On second thought, it's easy to do in a single view, and you can even let Tableau calculate the running totals using a quick table calculation (or you can use your own cumulative totals).

             

            single view.png

            • 3. Re: Two Bars and Two Lines in one chart.
              Naveetha Donthi-Reddy

              Hello Zach!

               

              Thank you for your response!

               

              Yes! that's one of the approaches we can go for. However, My client wants bars and lines in one chart.

               

              -Naveetha!

              • 4. Re: Two Bars and Two Lines in one chart.
                Zach Leber

                The second view is the best Tableau can do. It won't let you put two measure bars side by side with two measure lines on top of the bars. I don't think that would be a good way to look at the data anyway.

                • 5. Re: Two Bars and Two Lines in one chart.
                  Naveetha Donthi-Reddy

                  I haven't decided on how to get Cumulative values knowing the fact that Tableau can do calculations.  I can get it from source or use calculations in Tableau(This part is easy).

                  At this point, I want to know how to get bars and lines in one chart.

                   

                  -Thank you!

                  -N.

                  • 6. Re: Two Bars and Two Lines in one chart.
                    Naveetha Donthi-Reddy

                    I understand. But, that's the requirement as of now.

                     

                    -Thank you,

                    -N.

                    • 7. Re: Two Bars and Two Lines in one chart.
                      Jonathan Drummey

                      Hi,

                       

                      I agree w/Zach that grouped bars w/multiple lines is generally not a good way to look at the data, especially since with Tableau we have options for interactivity like viz in tooltip & filter actions that can help users make the necessary comparisons without requiring overloading a single chart. If you really need to go this route I can think of three ways:

                       

                      1) Pivot or self-union the data so you can turn one at least set of measures (bars or lines) into a dimension+value and then do it. http://drawingwithnumbers.artisart.org/bars-and-lines has guidance on how to do this. Note that the post talks about Custom SQL, that's often no longer necessary since Tableau has added the pivot & union features.

                      2) If you're not able to do that data preparation then you can sometimes use a data blend for that preparation by building a data source that has all of the dimensions you need (the dates plus rows for the measures).

                      3) With Tableau v2018.3's new transparent backgrounds feature sometimes it's possible to overlay axes by building one chart as a base layer and then a second chart with a transparent background, put both on a dashboard, and float the second chart over the first then fiddle with alignment. That can get pretty complicated, I haven't tried it for this particular scenario, though, so the fiddly bits with alignment could be *very* fiddly.

                       

                      Jonathan

                      • 8. Re: Two Bars and Two Lines in one chart.
                        Naveetha Donthi-Reddy

                        Thank you for your response! I will look into options you provided.

                         

                        -Thank you,

                        -N.

                        • 9. Re: Two Bars and Two Lines in one chart.
                          Naveetha Donthi-Reddy

                          Hi,

                           

                          I was able to achieve my requirement with Option#1(Pivot). However, My bars look stacked even after I make option "Stack Marks" to "Off'.

                          • 10. Re: Two Bars and Two Lines in one chart.
                            Jonathan Drummey

                            Hi,

                             

                            Without seeing a screenshot of the view, a Tableau workbook, etc. I can’t help you, I’m sorry. At the very least I’d need a screenshot of the entire view (including all the shelves) as well as the formulas of the calculation(s), ideally you’d post a Tableau packaged workbook with sample data showing the problem, then I could take a look.

                             

                            Thanks,

                             

                            Jonathan

                            • 11. Re: Two Bars and Two Lines in one chart.
                              Naveetha Donthi-Reddy

                              Hi,

                               

                              Can you please take a look at the Packaged workbook? Thank you!

                               

                               

                              -Naveetha.

                              • 12. Re: Two Bars and Two Lines in one chart.
                                Jonathan Drummey

                                Hi,

                                 

                                Thanks for the workbook, I could spot a few issues and fix them.

                                 

                                The initial issue was one of terminology: There are two definitions of stacking aka "marks on top of one another". In general when we are referring to Tableau's mark stacking we're referring to how Tableau is laying out the marks, for example with a continuous measure on Rows then one bar segment appearing a coordinate Y and then the next bar segment appearing at coordinate Y+M where M is the length of the first bar segment. The second definition is that marks are overlapping on top of one other. In the supplied workbook the marks are not stacking, but they are overlapping.

                                 

                                Here you go:

                                 

                                 

                                 

                                Here are the changes that I made:

                                 

                                - Added two parameters for width & spacing. These are in units of days.

                                - Changed the DateAxis pill on Columns to use the Exact Date instead of the date value MONTH(). The reason why we need to use the exact date is that even though the DateAxis is offsetting the values around the month that's removed by the date value MONTH() setting which truncates to the month. In the final view this will also have tooltips turned off.

                                - Edited the axis to have major tick marks on quarters and minor on months, instead of Tableau's default. This is key - with exact dates Tableau's default tick marks generally won't be on the start of month, so we need to override that for display purposes.

                                - Created a custom Date (Months) pill to use with the ATTR() aggregation on Tooltip so we can show the month in the tooltip.

                                - Edited the DateAxis formula to use the two new parameters:

                                 

                                if [MarkType]="BAR" THEN

                                ( case [Type]

                                when 'Committed to Date' THEN DATETRUNC('month',[Date])+ [Width (days)] * 0.5 + [Spacing (days)] * 0.5

                                when 'Current Owner Budget' THEN DATETRUNC('month',[Date])+[Width (days)] * 1.5 + [Spacing (days)] * 1.5

                                when 'Actual' THEN DATETRUNC('month',[Date]) - [Width (days)] * 0.5

                                when 'Forecast' THEN DATETRUNC('month',[Date]) - [Width (days)] * 1.5 - [Spacing (days)] * 1.5

                                END)

                                ELSE

                                ( DATETRUNC('month',[Date]))

                                END

                                 

                                If you need to rearrange the x-position of the bars then you'd need to edit the above calculation to appropriately position the bars.

                                 

                                - Then for the bar marks set the Size to use the Width (days) parameter instead of the original measure (note that with the original measure the offsets in the DateAxis would have had to be adjusted for the width set by the MAIDEN measure).

                                - Then clicked on Size, changed the Size to Fixed, with alignment center. This is using the mark sizing feature introduced last year that wasn't available when I'd published the bars & lines workbook.

                                 

                                Here's the view:

                                 

                                Screen Shot 2019-02-22 at 12.26.48 PM.png

                                 

                                Also by using the mark sizing feature the view scales better when ranges are filtered, for example here's just 2018:

                                 

                                Screen Shot 2019-02-22 at 12.37.24 PM.png

                                 

                                I've attached the workbook.

                                 

                                Now I'm going to get on my data visualization principles soapbox: When I look at this visualization and consider various business questions this is an absolutely terrible visualization. For example "what is the trend of Committed to Date?" requires trying to find a lot of really skinny red bars and figure out where they are underneath the lines. Or "How different is the Current Owner Budget vs. Actual?" There are many visualizations (or combinations of visualizations with interactivity) that would be faster & more accurate (i.e. better) at answering specific questions than this one.

                                 

                                Jonathan

                                • 13. Re: Two Bars and Two Lines in one chart.
                                  Naveetha Donthi-Reddy

                                  Jonathan,

                                   

                                  Thank you so much for your help. I truly appreciate it! I can't see attached workbook.

                                   

                                  -Thank you,

                                  -Naveetha.

                                  • 14. Re: Two Bars and Two Lines in one chart.
                                    Naveetha Donthi-Reddy

                                    Hi,

                                     

                                    Can you please attach workbook?

                                    1 2 Previous Next