Dynamic secondary axis titles (in a few more minutes)

In a comment, Joe Mako used his pixel ruler to identify some changes to make the final dashboard tighter and more accurate, I’ve edited the instructions to include this.

A couple of days ago, the Andy Kriebel (follow his blog if you aren’t already) posted Dynamic axis selections with parameters in less than five minutes. This technique enables users to pick the measure they want to view. The super-short version of the instructions are:

  1. Create a parameter to choose the measure.
  2. Create a calculated field to return the chosen measure.
  3. Put the calculated field on the Rows or Columns Shelf to generate the marks.
  4. Put the parameter on the Rows or Columns Shelf to create a header to use as the axis title.
  5. Delete Tableau’s default axis title.

This works great. However, when you want a dual axis chart with dynamic secondary axes, there’s a problem – there’s no way to tell Tableau that you want it to draw a header on the opposite side of the chart from the other header(s) and get the secondary axis title to accurately reflect the chosen measure.

Dashboards to the rescue!

Instead of a single view, you can use a dashboard with two (or three) worksheets. One worksheet has the left-hand (or top) headers, the left-hand – aka primary – axis with a dynamic axis title as described above, the chart, and the right-hand (or bottom) – aka secondary – axis tick marks. The second worksheet has the dynamic secondary axis title only and is formatted to size and resize the same as the first worksheet. In the dashboard below, I added a third worksheet to have a color legend that would show all the possible measure selections at once:

Here’s how to build the dashboard, assuming you’ve already got a worksheet with a view as Andy described.

  1. Create a “Choose 2nd Measure” parameter and “2nd Value” calculated fields to set up the 2nd measure.
  2. Add those to the worksheet to create a dual axis chart.
  3. Create “Choose Measure Color” and “Choose 2nd Measure Color” calculated fields that just return the value of each parameter.
  4. For each mark, put the appropriate color calculated field on the Colors Shelf.
  5. Select a parameter option for one of the parameters and go to the Color Legend to edit the color. Repeat for every parameter option until all colors are set the way you want. Note: It would seem like you could just use the parameter to set the color, however Tableau does not always remember your customer color settings, so that’s why there are calculated fields for the colors.
  6. To increase legibility, I also clicked on the Color Shelf on the 2nd mark type to increase the transparency of the 2nd measure.
  7. Set up any other formatting that you need on this dual axis worksheet.
  8. Duplicate the dual axis worksheet in order to capture all formatting/sizing.
  9. Drag the Choose 2nd Measure parameter onto the “Choose Measure” blue pill on Rows to replace it.
  10. Click on the Choose 2nd Measure pill to Format->Alignment for two things: Direction->vertical/down text and Horizontal->left. This gets us text aligned to face the chart, and the left alignment gets the text an extra pixel closer.
  11. Click on the Customer Segment pill to uncheck Show Header.
  12. Drag one of the green pills off the chart.
  13. On the Marks card, change the Mark type to pie and drag the Size slider at the bottom of the card all the way to the left. This creates an invisible pie chart.
  14. Create a calculated field called “Fake Header” with the text ” ” (just a space). Then drag that field on top of the YEAR(Date) field. This is to ensure that the top margin of the column of fake axis titles is properly set..
  15. Duplicate the Fake Header field and drag that onto the Columns shelf, this will create a bottom header that is to ensure the bottom margin of the column of fake axis titles is properly set.
  16. Unchecking Show Header for the Fake Header or hiding Column labels would end up rearranging the sizing, so we can’t use those. Instead, in the view right-click on the Fake Header header, and choose Format. In the Format Field Labels window, change the Font for the Columns to white.
  17. Do the  same for the Fake Header (copy) that you created.
  18. Format->Lines->Sheet tab->Axis Ticks to None.
  19. Drag the right edge of the worksheet to the left to minimize the space taken up by the chart area.
  20. In the Dashboard Menu, choose New Dashboard.
  21. If you are using a fixed dashboard size, set it now.
  22. Drag a Horizontal Layout Container into the view.
  23. Drag the initial dual axis worksheet into the view.
  24. Drag the 2nd header worksheet into the view, just to the right of the dual axis chart.
  25. Turn off to the titles for both worksheets.
  26. Now for the futzing bit. You may need to adjust the borders of the top and bottom headers of both worksheets to get the 2nd header fake axis titles to be in the same position within each pane. to fit, especially when you are using a fixed dashboard size.
  27. Turn off the borders for the 2nd header fake axis title by going to the worksheet and choosing Format->Borders->Row Divider->Pane: None and …Column Divider->Pane: None.
  28. Back in the dashboard, click on the 2nd header worksheet and resize the width so it takes up less space.

For setting up the Color legend, I used a separate worksheet using Square marks with Measure Names on the Rows, Color, and Label Shelves, and a fake axis so I could control the alignment. See the Tableau Public workbook above for more details.

 

Build Your Own Color Legend

 

And there you have it. Thanks to Andy for the inspiration, and thanks to the Tableau Community forums for the initial question – the workbook I used here is based on one I’d created awhile back for this Tableau forum post: Dynamic header names.

9 thoughts on “Dynamic secondary axis titles (in a few more minutes)

  1. Joe Mako

    You really only need one measure pill on the “Dual Axis 2nd Header” sheet, and it can be any green measure pill.

    Also, you will want to adjust the vertical height of your header, so it lines up with the Year header (note that you need to adjust both to get it perfectly aligned). Currently, it is clearly obvious that they are not aligned. As for mark type, if you choose Pie, and more the slider to the lowest setting, the mark disappears (see I use pie charts, one invisible ones though). You can also set the horizontal alignment of your generated axis label to be Left instead of automatic, getting it a couple of pixels closer.

    You can download http://public.tableausoftware.com/views/dynamicaxistitlesjmedit/Dashboard3 and switch between the two dashboards to see the slight difference.

    Reply
    1. Jonathan Drummey Post author

      Hi Joe,

      Thanks for the suggestions! I’ve incorporated them into the text above.

      The hack-y nature of this workaround is very much revealed in the vertical alignment problem you’d noted. At the last minute I’d decided to fix the dashboard size at 600×400 to fit better in the blog layout and that altered the alignment and I didn’t catch it.

      I didn’t know about the invisible pie chart trick, that’s handy. I especially like making the mark so small nobody can see it (or click on it by accident).

      Reply
    1. Joe Mako

      Andy,

      Yes both posts use a parameter to switch out a value used in a pill, a very useful fundamental technique.

      Jonathan’s post, and my adjustment, deal with a minor aspect that was not addressed in your post.

      Currently, Tableau does not enable a dynamic axis title, in the Edit Axis dialog, you cannot have the title driven by a parameter, or anything other other than a static value, or just based on the pill name.

      The workaround of using a dashboard that Jonathan has gone through here gets us as close as currently possible to getting a dynamic axis title on the y-axis of the secondary dual-axis. You can take the technique he has gone through here and apply it to creating a dynamic x-axis title.

      In your NBA chart, you turned off the axis titles for your x-axis, or used the parameter to create a cell header that is not near the axis.

      Reply
    2. Jonathan Drummey Post author

      Hi Andy,

      I can see how they are similar, the key difference being what Joe noted: what I was documenting was not so much how to make the secondary axis title dynamic, moreso how to make it visually appear like the primary axis title that you’d described how to create in the post that inspired me.

      One of the things I love about the Tableau community is how we do a lot of plussing. You built on Andy Cotgreave’s post, I built on yours, Joe added a few ingredients, and voila!

      Jonathan

      Reply
  2. Karthik

    is there is a way to dynamically change the date axis? i.e Week-wise or Month-Wise based on the user selection. In my report i have Sale-date in y-axis and daily Sales in x-axis for the whole year.

    Reply
    1. Jonathan Drummey Post author

      Presuming that you have a single date field and you want to aggregate that at different levels, you can create a parameter with values of quarter, month, month, etc., then a calculated field that does something like DATETRUNC([myParameter], [myDateField]). One potential problem with this is the axis tick marks, Tableau may not behave exactly the way that you want. If you want total control over the axes, then you’d need to use separate worksheets in a layout container and use the parameter to swap between them, that technique is outlined in http://kb.tableausoftware.com/articles/knowledgebase/creating-sheet-selector-for-dashboard.

      Reply

Leave a Reply to Shawn Wallwork Cancel reply