7 Replies Latest reply on Jun 4, 2013 10:47 PM by Jim Wahl

    Labelleing dual axis charts and handling negative values

    ruth.wyer

      Hi,

       

      I connect to a cube in Tableau 7 and am showing a straightforward chart with sum of balance and count of records.  The balance axis defaults to negative values (not sure why as the balance is always positive?).  The charts look very messy when I have a count of records with no associated balance, the balance sits halfway up the chart above the count.  It is even worse when I label the balance (which I need to do as the balance is often too small to easily be read using the axis alone. 

       

      I have two issues here:

      - I don't seem to be able to make the right axis start at zero without fixing the upper limit (I don't want to do that as when the data refreshes the axis doesn't adjust to the new values)

      - the labelling is very limited (always has to sit near top of bars unless it is zero in which case it seems to sit below the bar).  If you have dual axes the points tend to intersect at the same spot which makes it hard to read the labels.  Would be much easier to read if the bar charts could be labelled at the base.

       

      Can anyone shed any light on this?

       

      Thanks

      Ruth

        • 1. Re: Labelleing dual axis charts and handling negative values
          Jim Wahl

          Hi Ruth,

           

          - I don't seem to be able to make the right axis start at zero without fixing the upper limit (I don't want to do that as when the data refreshes the axis doesn't adjust to the new values)

          You've probably tried this, but what about selecting the option "Uniform axis range for all rows or columns" in the Edit Axis dialog box?

           

          - the labelling is very limited (always has to sit near top of bars unless it is zero in which case it seems to sit below the bar).  If you have dual axes the points tend to intersect at the same spot which makes it hard to read the labels.  Would be much easier to read if the bar charts could be labelled at the base.

          You can put the labels at the base of the bars by clicking on the label box and selecting alignment > bottom. If you want the labels to be on top of each other, you can drag both balance and count of records to the labels shelf for the bar chart marks. Tableau 8 gives you similar formatting options for labels as you had for tool tips in Tableau 7, so you can add additional text or color formatting to the values to guide the reader.

           

          Or you could put a data table below the graphs using a dashboard. ...

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Labelleing dual axis charts and handling negative values
            Shawn Wallwork

            Ruth, do you see the Include Zero check box?

             

            Include Zero.png

             

            That should help with part of your question. You aren't really getting negative numbers. What's happening is the zeros on the two sides of the chart aren't lining up so it gives the impression you have negative values. You might be able to clean this up a bit by formatting the axis and forcing alignment to the top:

             

            Format.png

             

            I didn't have any zero value data handy so I wasn't able to test this. If this doesn't work, then you might be able to create a calculated field that only labels when the value is greater than zero. But I haven't tested this either. Hope this helps.

             

            --Shawn

            1 of 1 people found this helpful
            • 3. Re: Labelleing dual axis charts and handling negative values
              Jonathan Drummey

              I run into the "no data getting a zero axis in the middle" issue as well here and there. One hack for making a dynamic axis is to use an invisible reference line, since unless you've fixed the axis size Tableau will resize the axis to include the marks and any reference lines. I set one up in the attached that uses a table calculation to get the maximum sales from any value, then use 1/4 of that value. You don't have to use a table calc, you could use a constant as well.

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Labelleing dual axis charts and handling negative values
                ruth.wyer


                Thanks you everyone for your replies.

                 

                Jim, the vertical alignment doesn't seem to do anything when I press it.  It doesn't on any of my tableau workbooks, including ones that aren't cubes.  A table below chart might work though.  Thanks,

                 

                Shawn, Yes, I do have that box but checking and unchecking it makes no difference to graphs.  I don't have the same options as you regarding axis formatting (perhaps you are on version 8?) so changing my axis to TOP looked a little different but didn't make any difference.  Thanks for attaching screenshots.  Very helpful.  Not sure if I can chart only if above zero as I needed to create a calculated field for Null to Zero to force months to appear on the axis when there was no data.  I'll give it a go though

                 

                Jonathan, Thanks for the workbook but unfortunately I could not open it.  Just received an error saying it could not load successfully.  I will give the reference line option a go.

                 

                Thanks again for all your help

                • 5. Re: Re: Labelleing dual axis charts and handling negative values
                  Jim Wahl

                  Hi Ruth,

                   

                  The behavior for label alignment changed between Tableau V7 and V8.

                   

                  V7's label alignment is a little funky---seems like it aligns around the center point of the mark. V8 aligns around the entire mark shape. Check out the attached images (same formatting, just one is opened in V8). It also looks better (only in V8) when the labels are on top of the bars.

                   

                  If you're limited to V7, the data table seems like a better option.

                   

                  Check out the attached workbook, which also includes Jonathan's neat reference line trick to push 0 to the bottom---assuming you don't want uniform axes for the graphs.

                   

                  Jim

                  1 of 1 people found this helpful
                  • 6. Re: Re: Re: Labelleing dual axis charts and handling negative values
                    ruth.wyer

                    Hi Jim,

                     

                    Thanks for the attachments.  They will be great for me to keep as reference.  I couldn't get it to work (perhaps because I am connecting to a cube and/or have calcs to turn nulls to zeros?).  But at least now I understand what is happening.  I like the calc to make the labels appear underneath each other but unfortunately can't do it with a cube as cannot create calculated fields in dimensions.   This is very useful to have though so thank you very much for sending.

                     

                    Ruth

                    • 7. Re: Re: Re: Labelleing dual axis charts and handling negative values
                      Jim Wahl

                      No problem; sorry it wasn't more helpful. I've never used cubes with Tableau and I'm continually surprised by the limitations (or differences ).