10 Replies Latest reply on Sep 27, 2016 12:04 PM by Jeffrey Shaffer

    Area Chart with 2 colors for positive / negative values

    Tom Smith

      Hi all,

       

      I have an area chart (see attached) that has positive and negative values. I'm trying to color the area above 0 green and below 0 red. I've tried creating a calculated field with an if statement and then adding that to the color field, but it does not work. Is this even possible in tableau?

      Thanks

       

      Screen Shot 2012-12-01 at 11.17.14 PM.png

        • 1. Re: Area Chart with 2 colors for positive / negative values
          Alex Kerin

          I thought this would be trivial - that you could easily color the area. Turns out not to be the case. I ended up splitting the measure into two variables, but even with the "else 0" in the calcs the plot isn't perfect.

          • 2. Re: Area Chart with 2 colors for positive / negative values
            Alex Kerin

            I'm not really sure what Tableau thinks it's coloring.

            • 3. Re: Area Chart with 2 colors for positive / negative values
              Jonathan Drummey

              I saw this and thought I'd take a swing at it, I'm seeing the same 0 problem as well. I set up a workbook with different attempts and a data set built to set up the issue. Here are the different worksheets:

               

              - shapes correct - shows the expected shapes based on the data. The goal is to have all shapes below 0 to be red, all shapes above 0 to be green.

              - stepped color agg doesn't work - Here I put the continuous SUM(Y) on Color and set it to be a stepped color, and it just shows green.

              - stepped color dim doesn't work - Using Y as a continuous dimension on color, the area chart turns into blocks, but does actually get the colors right.

              - color at row-level - here I created a row-level calc to check the value of Y, it draws a big green block at the bottom and draws red shapes that are the duplicate of the (correct) green shapes. You'll have to see it to see how messed up it is.

              - color at agg-level - here I created an aggregate calc on SUM(Y). The tooltip shows the proper Color Aggregate value, but the shapes are not colored.

              - two measures, invalid shape - In this one, I used Alex's idea of creating two measures that return positive Y values (and 0 for everything else) or negative Y values (and 0 for everything else). This works fine except for X=10 through 12, where the Y values go from -1 to 1 to -1, i.e. crossing the 0 Y axis. The slopes of the two red and one green shapes here are too flat, they need to be steeper so there are continuous lines. Flip back and forth between one of the other worksheets and this one to see how the shapes are incorrect.

               

              I played around with a workaround using table calcs to get the slopes to draw properly, however it would require more points to draw them properly. I think this is some combination of design behavior and/or a bug, I'm going to submit it to Tableau tech support.

               

              Jonathan

              • 4. Re: Area Chart with 2 colors for positive / negative values
                Alex Kerin

                Agreed. Odd we haven't seen it before.

                • 5. Re: Area Chart with 2 colors for positive / negative values
                  Tom Smith

                  Thanks for your efforts on this.

                  • 6. Re: Area Chart with 2 colors for positive / negative values
                    Jonathan Drummey

                    Hi Tom,

                     

                    I've heard a bit from Tableau tech support and this doesn't seem to be possible out of the box. The way area charts are drawn, there seems to be one color for each area and that's it. However, I got stubborn and made this work:

                     

                    area chart.gif

                     

                    The "trick" was to generate polygons and work out the calcs, it's difficult because the shapes change when the line from the prior and/or next points cross the X axis. Here's a short bit on how I did this, I'll probably turn it into a longer blog post:

                     

                    1. Create an area chart. This serves as a template to make sure the shapes are correctly drawn. I set up the demo data to test out a bunch of conditions, there may very well be conditions I hadn't anticipated so having something to verify against is useful.

                     

                    2. Duplicate the data source.

                     

                    3. Edit the copied data source to add a field called Point ID and add 3 UNION queries to duplicate the original query and end up with 4 Point IDs. Most of the time we're just drawing triangles, however sometimes there are quadrilaterals, and Tableau needs as many points as there are sides.

                     

                    4. Generate the calculations. There are several, all using table calculations that depend on some sort of unique ID for each mark you are going to draw. In the sample data, the field is ID.

                    • Crosses X Axis - determines whether the line from this point to the next point (based on the X axis) crosses the X axis, useful for identifying whether the polygon needs to take into account the slope of the line
                    • Slope - the slope of the line from this point to the next point
                    • Polygon Type - I identified 10 different types of polygons, hopefully that's enough for this case!
                    • Plot X - The X location to plot points, this will go on the Columns Shelf. This uses a set of CASE and IF statements based on the Polygon Type and Slope.
                    • Plot Y - The Y location to plot points, this will go on the Rows Shelf.
                    • Color - The whole point of this exercise.

                     

                    If you are going to use these calculations, I suggest you copy the calcs from this worksheet out to Notepad or something similar, do a copy & replace on ATTR([X]) and ATTR([Y]) with the measures of your choice, and then paste them into new calcs in your workbook. That way Tableau won't replace the [X], [Y], etc. with things like [Calculation_837367171].

                     

                    5. Create a view with ID on the Level of Detail Shelf, Point ID on the Path Shelf, Plot X on Columns, Plot Y on Rows, Color on the Color Shelf, and all the table calcs set to Compute Using ID.

                     

                    A few other things of note:

                     

                    • Tableau appears to draw faint borders around polygons where they overlap, I haven't found any way to remove that to create continuous shapes.
                    • Even though the polygons are set to draw along the 0 axis, they hover an extra pixel over the zero line as compared to the Tableau-generated area chart you can see in the 1. Raw Area Chart worksheet.
                    • The Tableau-generated area chart will draw a line where there two successive points are along the 0 axis - such as 0 to 1 to 2 in the 1. Raw Area Chart worksheet - whereas the polygon does not draw anything.

                     

                    Let me know if this is useful to you!

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: Area Chart with 2 colors for positive / negative values
                      Tom Smith

                      Thanks for all the effort on this.

                       

                      I hope in a future version of Tableau this will be much simpler.

                      • 9. Re: Area Chart with 2 colors for positive / negative values
                        Robert Rouse

                        I think there's a much simpler way with no complex calcs and two axes. The top has an axis range with a fixed start of 0 and the end is automatic. The bottom axis ranges from automatic to 0 as a fixed end. Then you can color each independently. See this image and attached workbook for the setup.

                         

                        One detail on this is that you see "0" labeled twice. You can tweak that by setting one of them at some very small decimal like 0.000001.That's less than 1 pixel visually and tells Tableau not to label it at 0.

                        two axes.JPG

                        2 of 2 people found this helpful
                        • 10. Re: Area Chart with 2 colors for positive / negative values
                          Jeffrey Shaffer

                          I meant to post this solution last night. Looks like Robert beat me to it. Here is a reference to the tip (this is Tip #1).

                          http://www.dataplusscience.com/TableauTips5.html

                           

                          Also, if you apply Tip #9, then that will solve the double zero without having to set a really small decimal place. Simply custom number format to $#,#;;#.

                          1 of 1 people found this helpful