9 Replies Latest reply on Feb 3, 2015 4:24 PM by tyler garrett

    Area Graph with dual axis Lines

    Daniel Vincent

      I want to create a viz like this:

      Screen Shot 2012-12-27 at 1.16.17 PM.png

       

      The area is based on two measures called Total_Success (blue) and Total_Processed (yellow).  Total_Success is a calculated measure based on (Total_Processed-(sum(errors))

       

      The lines are individual error percentages.  i.e. err1_pct; err2_pct, etc.

       

      I've been able to create a dual axis viz based on a single measure for the area chart and all of the error percentage lines.  However, I've run into a snag when attempting to get the second measure included within the area chart.

       

      Screen Shot 2012-12-27 at 1.15.38 PM.png

       

      Any suggestions for a solution?  I know I'm close but can't seem to crack it.  Any help is appreciated.

       

      *I can't share the data/twbx due to confidentiality

        • 1. Re: Area Graph with dual axis Lines
          Catherine Rivier

          Hi,

          The issue here is you can't use Measure Values twice in the same view, using different measures.  You essentially want the left axis to have Total Success and Total Processed, and the right to have percentages.

           

          You'll likely either need to decide on only one or the other having multiple measures; or you can try for a workaround that might be possible since one numeric group is percentages (so will be <1) and the other, hopefully, large enough numbers that will make this work.

           

          See the attached worksheet for this sheet.  The first two (Area graph alone (#) and Line graph alone (%)) just show the 2 pieces alone of what I'm using in the final combined view.  The large numbers are in the Area Graph, and are Sales and Profit.  The percentages are in Line Graph, and are some made up numbers - all under 1, of course.

          Combined View combines these two.  The key here is:

          1. ALL of the numbers you want to see - large numbers and percentages - are in the Measure Values page.
          2. Measure Values is on both sides of the dual axis.
          3. Changing Marks to Multiple Mark Types, the original Measure Values is an area graph, version (2) is a line graph.
          4. In the area graph, the percentages are so small, they will barely show up at the bottom of the area chart, not enough to be noticeable.  Nothing needs to be adjusted here.
          5. In the line chart, this is clearly not the case.  Instead, fix the right axis to only go from 0 to 1.  The non-percentage numbers then will not show up on the graph, unless they ever fall to 0.

           

          Hope this works for what you're trying to do!

          • 2. Re: Area Graph with dual axis Lines
            Daniel Vincent

            You have to be kidding me!  I was so close and messed with similar iterations.  Thank you so much as the work around you provided is what I was looking for.  You rock!

            • 3. Re: Area Graph with dual axis Lines
              Jonathan Drummey

              Catherine,

               

              This is brilliant! Much easier to implement than Custom SQL solutions I've used in the past (and one that I just suggested). I did some playing around and found that a minimum value for the Area Marks of 2000 was enough to keep the % lines from showing up at all, though that needed to be 2500 when I used Bar Marks. One advantage to this solution is that you have one color legend for both axes.

               

              A couple of other caveats I discovered: The high-value marks have to be Bars or Area marks, if they are another Mark Type then there will be marks drawn for the % measures - along the 0 line for. Conversely, the % marks have to be Lines or Circle/Square/Shape marks, if they are something else then there will be marks for the high-value measures.

               

              Jonathan

              • 4. Re: Area Graph with dual axis Lines
                Daniel Vincent

                Okay so it worked however now I've come across another problem. Whenever Total Success or Total Processed dip down to the percentage values they show up as lines. 

                 

                Screen Shot 2013-01-02 at 11.20.04 AM.png

                 

                But it should look like this:

                Screen Shot 2013-01-02 at 11.19.27 AM.png

                 

                Any ideas on how to address this? 

                • 5. Re: Area Graph with dual axis Lines
                  Daniel Vincent

                  Patience isn't a virtue of mine. 

                   

                  Just applied a simple IF statement and it should be a good work around (for now)

                   

                  if sum([Total Success]) <= 100 then 101

                  else sum([Total Success])

                  end

                  1 of 1 people found this helpful
                  • 6. Re: Area Graph with dual axis Lines
                    Catherine Rivier

                    Ha - well you did wait 9 minutes   Actually, your solution is pretty great.  The main issue with my original fix is that is a workaround, so needs new workarounds for any other problems, as you found.

                     

                    Here's one other option that could work:  increase all the values of those variables by a factor of 1000, and then adjust the axes so it looks like you didn't.  Make copies of each area graph variables that are the variables*1000, so sum([TotalSuccess])*1000 - plus a caveat IF/THEN to make sure 0's show up as 1000's.  So now you should have the same pattern as you would but the axis will show millions instead of thousands.  You can fix that in the axis formatting.  Change the number format to use the units of Millions, and add the suffix "K".  Then go into custom format and remove the "M" part of the formatting code (on my test it looked like #,##0,,"K";-#,##0,,"K"), and it'll look like thousands when it's really millions.  Another workaround, though a little more work, and different potential issues long term.

                     

                    One other note is, if you put the original variable in the Level of Detail, you can use those in the tool tip instead of the modified variable.  Like the modification was never done at all!

                    • 7. Re: Area Graph with dual axis Lines
                      Daniel Vincent

                      Thanks Catherine. 

                       

                      On a similar note with dual axis formatting.  How do I get one side to show numbers and the other percentage?  When using the format from the axis drop down it inherently formats both axes.

                       

                      I put these back to single axis just to reference what I'm talking about.  When I try to change just the line chart to percentages it changes the top numeric values as well.

                      Screen Shot 2013-01-02 at 3.32.54 PM.png

                       

                      Result

                      Screen Shot 2013-01-02 at 3.33.30 PM.png

                       

                      I went so far as making the measures as a percentage but that still didn't work.  Any ideas?

                      • 8. Re: Area Graph with dual axis Lines
                        Catherine Rivier

                        Shoot.  I don't know if that can be changed, since (as you found out) all of the Measure Values axes need the same scale format.

                         

                        The best I can offer is either leave as is (the percentage axis can be formatted as 1.0, 0.9, 0.8, etc., and most will understand).  You can also label the ends of the lines with percentages to make things clearer.

                         

                        Two more ideas take advantage of the fact that the percentage axis will be fixed, from 0-100%.  In both these, I hid the header on the percentage axis.

                        1. You can create a "fake axis" with the percentages you need, and combine them in a dashboard.  I did this in the attached using the average of Number of Records - a nice way to get 1.  With a little manipulation, and maybe some blank objects, you can get this to look like it's the original axis you intended.  (Tab New Combined View With Fake Axis)
                        2. Add a reference line at each percentage you want labeled.  Make them a constant value (0.8), with a custom label (80%), and no line.  Format so it's vertically centered.  Though I noticed 2 issues with this - it will also put these on the numeric axis so they will stack on the bottom left corner; and this will adjust this axis to start below 0 (which you can change manually, but this isn't ideal).  (Tab New Combined View With Reference Lines)

                         

                        Maybe someone else has an idea?

                        • 9. Re: Area Graph with dual axis Lines
                          tyler garrett

                          Digging around for some weird solutions and stumbled across this thread.

                           

                          Daniel Vincent (I would @tag you but there are 3 Daniel Vincents)


                          How do I get one side to show numbers and the other percentage?
                          Solution:

                          • Dual axis your measures
                          • Use quick table calc precentile
                            • or "RANK_PERCENTILE([Measure])" = your calc field
                          • Compute using Table Across

                          Dv solutiion.png

                          I know this is a late response, but I want others to be able to find the solution if they stumble upon this like me.


                          Cheers,

                           

                          Tyler Garrett

                          Senior Business Consultant

                          Professional Services

                          Tableau Software

                          Visualize and Understand Tableau Functions