November 28, 2011
There a good chance you’ve run into this scenario before, maybe in a past life in Excel or with Tableau: You have a chart with a bunch of bars or columns or maybe a line chart, and you want the top 5 values labeled. Ideally, the chart would look something like this:
With Tableau, you can manually assign labels to these points, but wouldn’t it be better for the points that are labeled to change dynamically based on the data you have selected? There’s no easy way to do this in Tableau, but as always, there’s a workaround that’s quite simple once you implement it once or twice.
Be patient as you read your way through this; it might seem a bit complicated, but I’ll detail every step. Let’s get started.
1. Drag the Order Date dimension onto the Columns shelf, right-click on the pill and choose All Values (This changes the Order Date field from a Discrete dimension to Continuous; not a critical step, simply personal preference)
2. Drag the Sales measure onto the Rows shelf (I’ve filtered the Order Date to 2010 only, but that’s not necessary either)
3. Create a parameter, I named mine Top X, with the following properties:
NOTE: I could have chosen to always label the top 5 or top 10 values, but I want the consumers of the dashboard to be able to select the number of values they want to see labeled, thus the need for a parameter.
4. Right-click on the “Top X” parameter and choose “Create Calculated Field…” Name the field “Top X Label” and enter this formula:
IF INDEX()<=[Top X] THEN SUM([Sales]) END
I’ll explain the need to use the INDEX function in a bit.
5. Right-click on the “Top X” parameter and choose “Show Parameter Control”
6. Drag the Top X Label calculated field (from step 4) onto the Label shelf on the Marks card.
7. Here comes the trick: Right-click on the Top X Label measure and choose “Edit Table Calculation”
8. In the Table Calculation dialog box, change the Compute Using option to Advanced
9. In the Advanced window, change the Order Along settings to the Sum of Sales Descending. This will force the Top X Label field to index the values based on Sales from highest to lowest (thus the reason the calculated field compares to the INDEX() function).
That’s it! The top 5 points are now labeled. You’re chart should look like this:
You can use the Top X parameter to pick the number of values you want to label. Even if you filter the data, maybe to only show the East Region, the labels will still work properly.
But this is Tableau, so let’s take it a step farther. Maybe you need to label the top 5 and the bottom 5. There’s a neat little way to do this too.
1. Duplicate the Top X parameter and rename it Bottom Y and the show the Bottom Y parameter control
2. Duplicate the Top X Label calculated field, update it to reference the Bottom Y parameter and rename it to Bottom Y label:
IF INDEX()<=[Bottom Y] THEN SUM([Sales]) END
So now what? There’s no way to add a second label! True, but there IS a way to add a secondary axis.
3. Drag Sales onto the Rows shelf, right-click on it and choose Dual Axis
4. Remove “Measure Names” from the color shelf (we don’t need different colors since we’re using the same measure twice)
5. On the Marks card, click on the carrot on the upper-right of the card and choose Multiple Mark Types
6. Click the right arrow twice until you see “SUM(Sales) (2)”, then drag the Bottom Y Labels calculated field onto the Label shelf
7. Right-click on the Bottom Y Labels field, choose Edit Table Calculation, then repeat steps 8 & 9 above. The only difference is that the advanced table calculation should be in ascending order for the Bottom Y Labels:
You’re done! You now have an interactive chart that allows the user to pick the number of top and bottom values they want to see. Interact with it, download it and see how it works for yourself.
Finally, I would like to thank Joe Mako for his help in walking through this situation. He helped me with the formula and advanced table calculation for the Top X Label field.
November 18, 2011
Overall, this chart is well executed, except I would have sorted the players in descending order. But I wanted to take it one step farther, so I downloaded the data from pro-football-reference.com and got to work in Tableau. I wanted to be able to compare:
- Not only the combined kick returns, but also the top punt returners and kickoff returners separately. I wanted to know which players were the best in each category.
- Players that played for one team versus more than one team
- A player’s kick return ability compare to his punt return ability
Finally, I wanted to be able to filter each chart by the Top X Players for that chart. This is where parameters come in handy.
I started this post by saying I learned a few things. I learned to:
- Make the user experience easier by creating a list of instructions like Steve Wexler at DataRevelations.com always does. Hover over the NFL logo to see the instructions for this viz.
- Filter by a Top X parameter when there’s more than one item on the color shelf (like on the Total TDs chart). Check out this discussion on the Tableau forum for an explanation (thanks to Joe Mako for the link and help making it work with a scatter plot).
To answer the question in the title of the viz, Devon Hester is very dangerous…a clear outlier, he’s a player that is “numerically distant” from the rest of the players.
November 10, 2011
Another great TDWI webinar today: “Effective presentation of analytical results” by Jonathan Koomey. Much of the content will be review for those that have read books by Edward Tufte and Stephen Few (many of the examples used were taken from Show Me the Numbers), but it’s always good to hear someone else’s take on effective presentations. My key takeaways:
- Know your audience - Think about what your audience cares about, and express your results in those terms
- Don’t forget the decision maker
- Use structured storytelling to present the key results
- Document your work (results & methodology) so that others can recreate it
- Break up graphs and tables into two categories
– Those that give you insights
– Those that help you tell your story to others
- Charts and figures must focus on the data
- Improving poor visualizations is a great way to teach best practices
– Even simple changes can make a huge difference in communication
- My favorite quote: “Data are dull only when chosen poorly and presented badly”
Download the presentation here or read it below.UPDATE (11/11/2011): The webinar video is now available for replay. Watch it here.
November 8, 2011
Lyndsay Wise of Wise Analytics and Francois Ajenstat of Tableau hosted a terrific webinar today about self-service BI. I especially appreciated the discussion about IT’s role as an enabling and support organization, rather than bureaucratic and controlling.
There are lots of battle wounds from sparring with IT about BI and if you are in an organization where it’s challenging to get IT support for self-service BI, rather than resistance, then this might be the ammunition you need.
You can download the presentation here or read it below.UPDATE (11/11/2011): The webinar video is now available for replay. Watch it here.
I’ve been adding customers to the Penetration Reporting I presented at TCC11 (word is spreading and people love it!), but there are often records in our internal systems that do not match up with the master address list for our customers from Nielsen Spectra.
One of the great features of Tableau is data blending and this project is a perfect example of how you might use it. I have my sales and internal customer list in SQL server, but the location information, including latitude/longitude are in an Excel file. Tableau allows me to integrate these data sources via a key field, store number in this case.
As I referred to earlier, this works perfectly for ~95% of the records, but there are ALWAYS stores in our internal system that do not exist in Spectra. Here’s a sample of missing stores:
To prepare the data to address this problem I take the following steps:
- Concatenate the address fields together in Excel with the formula CONCATENATE(TRIM(Address)," ,",TRIM(City),",",TRIM(State)," ",TRIM(Zip))
- Copy all of the rows in the new “Full Address” column
Now the fun and magical part begins. A colleague led me to the tool Batch Geocoding, which basically takes text strings and returns the latitude/longitude coordinates. Here’s how it works:
- In the “Input” box, paste the data you copied from Excel above
- Choose your output format and click the “geocode” button
- Watch the magic as the tool populates the Output box.
- Copy all records from the Output box and paste into Notepad
- Save the Notepad file in txt format
- Open the TXT file in Excel
- Copy the records from the TXT file and append to the end of the master customer list from Spectra
That’s it! So simple! I have no idea how it works, but it does. Of course, it’s not going to be 100% accurate, because the addresses may not exist, but it does tell you how well it was able to match the records.
Definitely keep this link in your toolkit. I’ve also added a link on the right side of this blog under “Useful Data Sources”.
November 7, 2011
One of the great things about the data viz world is that people are always willing to listen, learn and share. Cole Nussbaumer over at storytelling with data (a great blog you should follow) recently conducted a visual makeover on some horrible charts submitted during a class she was teaching. and she was willing to share her data with me so that I could make my own viz. Where else do you experience such camaraderie?
The improvements she recommends are fantastic, but I recommended one improvement to this chart she created:
Typically when I see side-by-side bar charts I’m looking to compare the bars that are next to each other. However, in this case, the bars are not necessarily related; they are simply a list of expenses and income next to each other. I recommended she create a waterfall chart like this one done with Tableau:
To me, a waterfall chart communicates the expenses vs. income story of this data more effectively
- The bar sizes make comparisons easy. It’s clear that Programs are the largest expense and Grants are the largest income.
- You can easily see the total variance without having to do the math in your head.
- Other Expenses are much larger than Other Income. I wonder what’s included in those expenses. Looks like an area for investigation.
- This group should probably focus a bit more on Sponsorships so that they’re not so dependent on Grants.
I could have included labels for all of the bars, but I wanted to show the patterns and relative sizes without the numbers being a distraction.
P.S. I chose red/green bars for two reasons: (1) most people understand red as negative and green as positive when reading financial figures and (2) to annoy my friend Steve Wexler of the Data Revelations blog (another you should follow), who hates this color scheme more than anyone I know.
November 4, 2011
TCC11 has passed, but everyone in attendance is anxious to get their hands on all of the great presentations, workbooks, etc. For those of you not able to go, now I’m making them available to you as well. Enjoy!
We packed the 2011 schedule with so many great sessions and learning opportunities that it may have been difficult to choose which session to attend. Visit our conference content page to watch the video recordings and download session materials. We will be adding more materials as they become available.
Save the Dates for our 2012 Tableau Customer Conference
European Customer Conference – April 2-4, 2012: W Hotel, Barcelona, Spain – apply to speak
North American Customer Conference – November 5-8, 2012: Hilton Bayfront, San Diego, CA
The next ATUG meeting will be November 17 @ 1PM ET
Who - All ATUG members and guests
What – The November in person hands on meeting
Where – Coca-Cola - 2 Coca Cola Plaza, Atlanta, GA 30313, USA-G
Important – You must check-in with security and tell them you are there for the Atlanta Tableau User Group meeting. They will then call me to come get you. I will have people in the lobby to help usher you to the room.
Plan to arrive at least 15 minutes early
1. Tableau Customer Conference Presentation – The Holy Grail of Strategic Decision Making (Andy Kriebel, Coca-Cola)
2. Training – Iron Viz Challenge: Create a beautiful viz with unfamiliar data in less than 30 minutes (David Newman, CSE Inc.)
3. Open forum –
- Tableau Customer Conference feedback, highlights, etc.
- December meeting – location, time, agenda
-- This will be a hands on session - Bring your laptop and Tableau with you --
November 3, 2011
Now that TCC11 has ended, the cat is out of the bag. Tableau 7 is coming, and coming soon; it should be available for download around the beginning of the year. Christian Chabot demoed many of the new features (but I’m sure not all) of Tableau 7 Desktop and Tableau 7 Server.
Some of my favorite new features (which we got to play with at TCC11) include:
- Filled maps!! (though they don’t go down to the zip code level)
- Wrapped maps
- Using parameters in filters, bins, reference lines/bands
- Improved statistics
- Lots of improvements to make the most prominent features more easily accessible
But there is one sad feature: the end of Null Island (this is the place where invalid geographic data are placed).
Here is what’s coming in Tableau Desktop:
Interested in learning what's coming in Tableau 7 Server? Click here.