4 Replies Latest reply on Apr 29, 2013 12:16 PM by Sarah Mostajeran

    Single view with two stacked bars side by side and include budget and net margin lines.

    Mike Janecek

      I am trying to build a dashboard that combines two stacked bars(actual & forecast) side by side.  I also want to include a budget line and a net margin line on the graph.  I have attached Excel version and data.  Thanks in advance for any help.

        • 1. Re: Single view with two stacked bars side by side and include budget and net margin lines.
          Mark Holtz

          Hi Mike,

           

          Couple thoughts and observations:

          1) Whenever possible, I'd suggest making your date values ALWAYS formatted as a Date data type. Yours are strings in Excel, I have created a field with what I mean called Date (type). You can always get the format you want in Tableau, and you get so much more power in terms of grouping, filtering, etc.

           

          2) In general, I'd also suggest that it's preferable to also eliminate blank lines between records, at least in your "raw data" table.  This will Tableau (and other users) read the file. You can always add additional white space and blank lines in a "display version" of the data.

           

          3) What I understand you're asking in terms of Tableau is essentially to use more than one Mark Type (4 Bars and 2 Lines) on the same view. But, I think you are asking to include multiple mark types (4 bars and 1 line) on the main $ axis, and then Net Margin as a line on a 2nd axis. Tableau does not permit you to use multiple mark types (4 bars and 1 line) for the special "Measure Values" field (which allows you to bring in several different measures at once). In fact, what you're REALLY asking to do the way your data is structured is to have 3 different sets of data (a pair of bars, another pair of bars, a line series of $) on the $ axis, and a 4th set as a %...

           

          That's a busy view in any case!  Is there perhaps a way you could break some parts off into separate pieces?

          You might be able to utilize Parameters to allow the user to toggle between some of the "pairs" you want to show.

          I've attached a sample of what I was able to quickly get with your data. 

          Sorry I couldn't be very much help!

          • 2. Re: Single view with two stacked bars side by side and include budget and net margin lines.
            Jonathan Drummey

            Excellent summary of the issues, Mark! Here's my contribution: Excel lets us individually format each data series that is drawn so it can do things like the chart you're trying to duplicate, whereas Tableau starts from the data. So what is a matter of editing a bunch of properties sheets in Excel can either be a split-second drag a pill into a view in Tableau, or take some reformatting and reshaping of the data to accomplish.

             

            Duplicating this chart took some of the latter, along with some help from Joe Mako to de-complicate what I'd put together.

             

            The first tack I took was to use the idea that in Tableau, you can create a grouped bar chart using a continuous value (dimension or measure) instead of a discrete value. The basic instructions are here:

            http://kb.tableausoftware.com/articles/knowledgebase/grouped-bar-chart

             

            Joe had also put together a variation here that involved some reshaping of the data:

            http://community.tableau.com/message/132454#132454

             

            Since dates can be continuous, I figured that instead of the above examples where a continuous number was used to generate the X axis, I could use the date +/- a few days to move the bars around. And where Joe's workbook had only drawn one line, I thought we could use Measure Values to draw multiple lines, and set up a demo of this in the attached workbook:

            grouped stacked bars and lines.png

             

            However, this runs into the problem that Mark had identified, which is that one of the lines is on the same axis range/number type as the bars ($$), and the other is a %.

             

            So, I tossed this attempt and went a different, more complicated route: Drawing all the lines and bars using Line marks, instead of using Tableau's Bar Marks. Think of a bar as simply a thick line drawn from point A to point B. Stacked bars are just lines that start at 0 or where the prior bar ends. Setting up this data required reshaping the data to have one row per date/measure/point, where there were two points per bar segment and the 8 or 12 points for the two lines. Also (this is where I got help from Joe), it's critical to have a unique segment ID, one for each line being drawn.

             

            The reshaped data then had the following elements:

             

            Date - the original date for each row

            Measure - these are the columns in the original data, turned into a single dimension

            Segment - The calculation for this happened in Excel, it could've been done in Tableau. It's the combination of the Measure & Date for the bar segments, and just the Measure for the line segments. This discrete field is used on the Level of Detail Shelf.

            Plot Date- the revised date so the bars would be grouped, this is the X axis.

            $$ Plotting Value - appropriately calculated to stack the bars, the basics of this were calculated in Excel. This is the Y axis.

            % Plotting Value - the value for the Net Margin measure, this is the secondary Y axis.

            Point Order - 0 or 1 for the bars, 1-8 or 1-12 for the lines. This was set up in Excel to be used as a dimension on the Path Shelf.

            Bar/Line - a calculated field in Tableau to use on the Size Shelf.

             

            From here, setting up the view is somewhat straightforward, you can see it in the attached. There are two key points:

            1. Use to use as few discrete dimensions as possible, in the attached workbook the only discrete dimensions are the Point Order and the Segment. Everything else uses ATTR() as an aggregation. The reason for this is that Tableau will generate a 'segment' for every combination of discrete values, and new panes for the discrete values on the Rows and Columns shelves, and both can cause lines to break into points. In this case, since the segment is defined using the Measure and Date, and all of the other elements that I wanted to bring int the view (Bar/Line for Size Shelf, Measure for Color Shelf, Plot Date for the X axis) were derived from those two values, the aggregations were easy to do.

             

            2. The z-order of the marks (what's on top/in front) is determined by the sort order, with whatever is sorted first being drawn on top. To not have the orange Budget line covered up by the marks, I had to sort the Point Order and Segment dimensions.

             

            3. Where there is 0 data, Tableau will still draw a point, you can see this on the first couple of columns. To change this, you'd need to filter out those values.

             

            This was fun to work out, I'll be putting together a blog post on it in a few days. I hope it's useful to you!

             

            Jonathan

            • 3. Re: Single view with two stacked bars side by side and include budget and net margin lines.
              Mike Janecek

              My thanks to all three of you for your help.  I was stuck on this chart for quite some time.  I was stuck in the same realm as Mark with using two panes to illustrate the data.  Applying attributes had never crossed my mind.

               

              Thanks again, maybe we'll cross paths in TCCC12.

               

              Mike

              • 4. Re: Single view with two stacked bars side by side and include budget and net margin lines.
                Sarah Mostajeran

                Hi Mike,

                I saw your post and noticed that you have been able to show two stacked charts side by side in different colors (Budget vs. Actual). Did you use any add-on in excel to do so? I have excel 2010 and haven't been able to figure a way to do so. Can you please let me know how you created your chart?

                 

                Many thanks!

                Sarah