February 22, 2017

## Overview

Earlier this week Gartner, Inc. published its “Magic Quadrant” report on Business Intelligence and Analytics (congratulations to Tableau for being cited as a leader for the fifth year in a row).

Coincidentally, this report came on the heels of one of my clients needing to create a scatterplot where there were four equally-sized quadrants even though the data did not lend itself to sitting in four equally-sized quadrants.

In this blog post we’ll look at the differences between a regular scatterplot  and a balanced quadrant scatterplot, and show how to create a self-adjusting balanced quadrant scatterplot  in Tableau using level-of-detail calculations and hidden reference lines.

Let’s start by looking at an example of a balanced quadrant chart.

Here’s the 2017 Gartner Magic Quadrant chart for Business Intelligence and Analytics.

Figure 1 — 2017 Gartner Magic Quadrant for Business Intelligence and Analytics

Notice that there aren’t measure numbers along the x-axis and y-axis so we don’t know what the values are for each dot.  Indeed, we don’t know how high and low your “Vision” and “Ability to Execute” scores need to be to fit into one of the four quadrants. We just know that anything above the horizontal line means a higher “Ability to Execute” and anything to the right of the vertical line means a higher “Completeness of Vision.”  That is, we see how the dots are positioned with respect to each other versus how far from 0 they are. Indeed, you could argue that the origin (0, 0) could be the dead center of the graph as opposed to the bottom left corner.

This balanced quadrant is attractive and easy to understand. Unfortunately, such a well-balanced scatterplot rarely occurs naturally as you will rarely have data that is equally distributed with respect to a KPI reference line.

## A Typical Scatterplot with Quadrants

Consider Figure 2 below where we compare the sum of Sales on the x-axis with the sum of Quantity on the Y-Axis. Each dot represents a different customer.

Figure 2 — Scatterplot comparing sales with quantity where each dot represents a customer.

Now let’s see what happens if we add Average reference lines and color the dots relative to these reference lines.

Figure 3 — Scatterplot with Average reference lines.

I think this looks just fine as it’s useful to see just how scattered the upper right quadrant is and just how tightly clustered the bottom left quadrant is. That said, if the values become more skewed it will become harder to see how the values fall into four separate quadrants and this is where balancing the quadrants can become very useful.

Note: The quadrant doesn’t have to be based on Average. You can use Median or any calculated KPI.

## “Eyeballing” what the axes should be

We’ll get to calculating the balanced axes values in a moment but for now let’s just “eyeball” the visualization and hard code minimum values for the x and y axes.

Let’s first deal with the x-axis.  The maximum value looks to be around \$3,000 and the average is at around \$500 so the difference between the average line and maximum is around \$2,500.

We need the difference between the average line and minimum value to also be \$2,500 so we need to change the x-axis so that it starts at -\$2,000 instead of 0.

Applying the same approach to the y-axis we see that the maximum value is around 34 and the average is around 11 yielding a difference of 23 (34 -11).  We need the y-axis to start at 23 units less than the average which would be -12 (11 – 23).

Here’s what the chart looks like with these hard-coded axes.

Figure 4 — Balanced quadrants using hard-coded axes values.

If we ditch the zero lines we’ll get a pretty good taste of what the final version will look like.

Figure 5 — Balanced quadrants with zero lines removed.

So, this works… in this one case. But what happens if we apply different filters?

We need to come up with a way to dynamically adjust the axes and we can in fact do this by adding hidden reference lines that are driven by level-of-detail calculations.

We need to come up with a way to calculate what the floor value should be for the x-axis and the y-axis.  The pseudocode for this is:

Figure out what the maximum value is and subtract the average line value, then, starting from the average line, subtract the difference we just computed.

Applying a little math, we end up with this:

-(Max Value) + (2*Average Value)

Let’s see if that passes the “smell” test for the y-axis.

-34 + (2*11) = -12

Now we need to translate this into a Tableau calculation.  Here’s the calculation to figure out the y-axis reference line.

Figure 6 — Formula for determining the y-axis reference line.

And here’s the same thing for the x-axis:

Figure 7 — Formula for determining the x-axis reference line.

Now we need to add both calculations onto Detail and then add reference lines as shown below.

Figure 8 — Adding the x-axis reference line. Notice that the line is currently visible. Further note that we could be using Max or Min instead of average as the value will stay the same no matter what.

Here’s what the resulting chart looks like with the zero lines and reference lines showing.

Figure 9 — Auto-adjusting balanced quadrant chart with visible reference lines and zero lines. The reference lines force the “floor” value Tableau uses to determine where the axes should start.

## Hiding the lines, ditching the tick marks, and changing the axes labels

Now all we need to do is attend to some cosmetics; specifically, we need to format the reference lines so there are no visible lines and no labels, as shown in Figure 10.

Figure 10 — Hiding lines and labels

Then we need to edit the axes labels and hide the tick marks as shown in Figure 11.

Figure 11 — Editing the axes labels and removing tick marks.

This will yield the auto-adjusting, balanced quadrant chart we see in Figure 12.

## Other Considerations

What happens if instead of the values spreading out in the upper right we get values that spread out in the bottom left?  In this case we would need to create a second set of hidden reference lines that force Tableau to draw axes that extend further up and to the right.

Also note that since we are using FIXED in our level-of-detail calculations we need to make sure any filters have been added to context so Tableau processes these first before performing the level-of-detail calculations.

Could I have used a table calculation instead of an LoD calc? I first tried a table calculation and ran into troubles with trying to specify an average for one aspect of the calculation and a maximum for another aspect using the reference line dialog box. I may have given up too early but got tired of fighting to make it work.

Note: Jonathan Drummey points out that we can in fact use INCLUDE instead of FIXED here so we would not have to use context filters. If you go this route make sure to edit the feeder calcs for the KPI Dots field ([Quantity  — Windows Average LoD] and [Sales  — Windows Average LoD]) so these use INCLUDE as well.

## Give it a try

Tagged with: , , , ,

## Overview

Prior to working the last two years with Jeffrey Shaffer and Andy Cotgreave on the upcoming The Big Book of Dashboards I tended to look at BANs — large, occasionally overstuffed Key Performance Indicators (KPIs) —  as ornamental rather than informational.  I thought they just took up space on a dashboard without adding much analysis.

I’ve changed my mind and now often recommend their use to my clients.

In this blog post we’ll see what BANs are and why they can be so useful.

## Examples of BANs

Here are several examples of dashboards featured in The Big Book of Dashboards, all of which use BANs.

Figure 1 — Complaints dashboard by Jeffrey Shaffer

Figure 2 — Agency Utilization dashboard by Vanessa Edwards

Figure 3 — Telecom Operator Executive dashboard by Mark Wevers / Dundas BI

## Why these BANs work

The BANs in these three dashboards are useful in that they provide key takeaways, context, and clarification. Let’s see how they do these things.

##### Key takeaways

If you had to summarize the first dashboard in a just a few words, how would you do that? The BANs shown in Figure 4 get right to the point.

Figure 4 — Concise complaints summary (we’ll discuss the colors in a moment).

The same can be said of the BANs in the Agency Utilization dashboards. By looking at the first two BANs in Figure 5, we can see that the agency made \$3.8 million but could make \$3.4 million more if it were to meet its billable goals.  That is the most important takeaway and it’s presented in big, bold numbers right at the top of the dashboard.

Figure 5 — If we had to distill this entire dashboard down to one key point, it would be that the current sales are \$3.8M but they could be \$3.4M more.

##### Context

The Three BANs in the Telecom Operator Executive dashboard (Figure 3) not only provide key takeaways but also provide context for the charts that appear to the right of each BAN.  Consider the strip shown in Figure 6 which starts with the proclamation that ARPU (Average Revenue Per User) is \$68.

Figure 6 — What contributes to Postpaid ARPU being \$68?

The images to the right explain everything that goes into making the \$68 (Comparison of Postpaid to Prepaid, Voice, Data, Addons breakdown, etc.)

Note that the dashboard designer packs a lot of very useful information into the box that surrounds the BAN; specifically, ARPU is up \$6 YTD, but is down in Q4 compared to Q3 (that’s how to interpret the line atop the shaded bars).

##### Clarification

The BANs in Figures 1 and 3 aren’t just conversation starters /  key takeaways, they are also color legends that clarify the color coding throughout the dashboard.

Consider the Complaints dashboard; the BANs indicate that Closed is teal and Open is red. Armed with this knowledge we know exactly what to make of the chart in Figure 7.

Figure 7 — Everything prior to November 2016 is closed and only a handful of things in November are open.

The same goes for the Agency Utilization dashboard. The BANs inform us that blue represents Fees and green represents Potential so I know exactly how to interpret bars that are those colors when I look at a chart like the one shown in Figure 8.

Figure 8 — Because the BANs told me how to interpret color, we can see that for Technology the company billed \$883K but could bill an additional \$1,762K if it were to hit its targets.

## Conclusion

BANs can do a lot to help people understand key components of your dashboard: they can be conversation starters (and finishers), provide context to adjacent charts, and serve as a universal color legend.

Note: While I’ve tried to show how effective BANs can be I did not address how a particular font can help / hurt your BAN initiative.

The Big Book of Dashboards co-author Jeff Shaffer has been studying font use and has a fascinating take on the new fonts Tableau added to their product this past year. You can read about it here.

Tagged with: , , , ,

February 10, 2017

Rosling was a Swedish professor of Global Health who, using data, stunning visualizations, and incredible charm, changed the way people understand the world.

I first became aware of his work six years ago when a friend showed me Rosling’s TED Talk from 2006.  If you’ve not seen it I encourage you to watch it now.

I’ve seen this video hundreds of times and it never fails to make me want to be better at what I do. It’s also a master class in how to give a truly great presentation.  I defy you not to be completely won over when he compares the knowledge chimpanzees have of the world with the knowledge the committee that awards the Nobel Prize in medicine has of the world.

Rosling was a true pioneer in using data — and in particular, visualizing data — to help correct peoples’ misperceptions about the world.  He didn’t set out to be a visualization visionary; he just realized that he needed to create new techniques so people would be able to better see and understand the world.  As he states in another of his videos, “having the data is not enough.  I have to show it in ways people both enjoy and understand.”

I had the pleasure of meeting Rosling at the 2014 Tableau Conference where Rosling was a keynote speaker.  I was invited to a special breakfast and was fortunate to be able to sit next to him.

He could not have been a more engaging and delightful dining companion. I told him that my daughter was majoring in Global Health and asked if I could take a picture with him. He gladly consented and suggested we pretend to be engaged in lively banter.

Me with Hans Rosling in 2014.

One thing I want to underscore about Rosling was his unbridled optimism for humankind. If somebody ignorantly claimed how bad things are compared with 50 years ago, he would counter with facts to show just how much better things are now. With fervor, he would cite amazing progress in eradicating malaria, educating young girls, lifting people out of poverty and decreasing the number of children born while increasing the average lifespan of people living in poor countries.

And he was steadfastly convinced that if we can fight ignorance and implement policies based on facts, the world will be a much better place in 50 years than it is now.

Let’s do what we can to prove him right.

Steve Wexler
February 10, 2017

Learn about Gapminder, an organization that Rosling co-founded with his wife and son.

Watch more of Rosling’s TED Talks.

Note: At the Tableau breakfast, when we went to sit down I offered my “prime” seat to Jock Mackinlay, Tableau’s VP of Research and Design.  Jock told me he had sat next to Rosling at the London Conference earlier that year and I should keep my seat.

Thank you, Jock.

Tagged with: , , ,