BI Commons

During last night’s Knicks - Pacers playoff game, J.R. Smith turned in a less-than-lackluster performance. Twitter account @DidJRSmithMiss live-tweeted the showcase and summed up the results at the end of the night:

Recap: yes, yes, yes, yes, no, yes, yes, yes, yes, yes, no, yes, yes, no, yes, yes, no, no, yes, no, yes, no.

— Did JR Smith Miss? (@DidJRSmithMiss) May 15, 2013

Here is his tweet visualized using QlikView:

I was looking through tweets about the NBA playoffs as it’s that time of year and stumbled upon Andrew Powell-Morse’s NBA Fines: Ballers Behaving Badly. In the post, Powell-Morse uses various data visualizations to illustrate how fines have been distributed across the NBA in the last decade. One chart in his post that stood out was the following straight table:

What struck me about the chart was the formatting used. By coloring specific regions and reducing ink in other areas, the chart organizes the information in a helpful way while increasing the presentation value of the overall image. Take the top 5 rows for example. Often at clients, I receive requests to illustrate the top 5 or 10 dimensional values based on a metric. This approach can feel limiting to myself and the client when there is plenty of other data to explore, especially if the top values do not change often. However, when I follow my natural instinct to add the entire list of values, still sorted by the metric, the users often feel lost. While adding the full list provides more data, it also provides more noise that drowns out the top values. It does not jump out to users immediately who the top 5 members are, even if the obvious explanation is that they are the top 5 rows.

Formatting the top rows separately provides a clever compromise to this problem. By highlighting top values with a different color, the table can provide the full list of values to explore while still intuitively presenting users with the top values that may be of interest.

The table utilizes some other interesting formatting techniques. Rather than highlight the entire row, only the dimensional value is highlighted, reducing the amount of ink necessary to prioritize the data. I also liked the organizing of the metrics and their percentages of the total, as well as the white space included between colored regions to keep the colors from bleeding into each other.

After looking at this table for a few minutes, I decided to try to produce a similar result in QlikView. First I loaded the source data and created the base chart without any formatting changes. All screenshots are taken in IE8 using AJAX.

Even with this simple set of changes, I had some struggles. For example, I had to use Custom Format Cell to alter the colors of the header and total row. This presented a problem when I tried to edit the total row on the bottom - it was not possible to select it in the Custom Format Cell menu. However, moving the total row to the top position gave me access in the menu and the ability to alter the formatting. Returning totals to the bottom of the table retained the formatting applied.

The result:

Halfway there. My final step was to add some of the special formatting from the original chart. This presented a special set of challenges:

• Coloring the top 5 dimension values - Using the rowno() function in conjunction with the dimension background formatting setting disabled my defined sort order. Replacing with rank(Expression) solved this problem
• Inserting white spaces between the columns - I accomplished this using empty expressions, but there is a limit to the minimum width of the columns that is undesirable
• Line coloring - Custom Format Cell allowed this, but I also had to manipulate the line coloring for the header and total rows. The combination of bottom and top border row colors across cells, headers, and rows was not obvious.
• Column formatting - I adjusted the formatting to clean up the presentation. This includes right aligning all of the numeric values and moving the “$” and “%” signs to the column headers.

The end result, with a title:

A decent amount of effort, but the end result is a dressed up table that may be suitable for a styled, presentable dashboard.

Some notes on the formatting constraints of QlikView:

• I couldn’t apply custom formatting to the total row like the header row. The total row seems to follow the formatting of the table cells, even if you change it with Custom Format Cell
• The column width minimum size is frustrating
• The header shares font settings with the table. In the QlikView chart, it would be nice if you could de-emphasize the headers by making them a lighter grey
• The lack of margins makes the table harder to read. Notice how poorly the dimension values fit inside the rows. The descenders of the font even cut off! This was true in both IE and Chrome.