Posts tagged as:

charting tools

Data Visualization: Who Needs a Reality Check?

by Jorge

According to Stephen Few, the founders of Tableau Software made some assumptions about visual analytics’ adoption that we can summarize in a single sentence: analysts want to find hidden insights in large and complex data sets using new visual paradigms. Later on, they discovered that these assumptions were somewhat flawed, and that what people really want is to save time in their daily routine when analyzing small and simple data sets, using familiar formats. Reality check, anyone?

We all make some wrong and costly assumptions. I wrote a blog on data visualization in Portuguese for about a year and then I had to give up, because no one seemed to care. I’m selling a tutorial on how to create Excel dashboards that I am proud of, but I should have started with a simpler version that delivers similar results (I’m working on that, by the way…).

Many of these assumptions are powered by what Chip and Dan Heath in Made to Stick call the “curse of knowledge” (“the better we get at generating great ideas—new insights and novel solutions—in our field of expertise, the more unnatural it becomes for us to communicate those ideas clearly”). Our wishful thinking makes us to believe that the knowledge gap is narrower than it really is, and some basic notions that we take for granted are not.

I’d love to write a blog on data visualization using higher-end tools like Tableau or Spotfire, but you can’t tell people “ditch Excel, use these great tools instead”. They have their (growing) market, but an overwhelming proportion of business charts are made in Excel because that’s the only tool people have access to. Excel is good enough to teach sound visualization principles, so visualization experts should start by saying “you can do it in Excel; here is how”. At some point the newly acquired knowledge would make people move up, if needed. In information visualization, we have the (graphic literacy-wise) rich and the poor. Now we need a solid middle class. Accessible learning tools is one of the answers.

(This is what I am trying to do with pie charts: instead of banning them, I’m trying to show how to create acceptable pie charts. At some point people will realize that they will need something better. I may be wrong, but the other options don’t seem to be working, either.)

If we fail to communicate this simple message (“you can do it in Excel; here is how”) do you know what we’ll get? A new Dundas/Crystal Xcelsius user.

{ 7 comments }

Video: Hans Rosling: Watch the end of poverty

by Jorge

{ 0 comments }

Using Google's Motion Chart to visualize population trends

by Jorge

In my Demographic Dashboard I have a scatter plot that shows the trend towards an aging society. Today I wanted to test the new Google Chart API and the Motion Chart with the same data set.

The chart displays the old dependency ratio against young dependency ratio. It clearly shows the shift from high ratios of young dependency to high ratios of old dependency specially in Europe and other developed countries. This means tough times ahead (the adult population will not be able to pay for social security systems, expensive medical care, etc.).

If you click a specific country a persistent label will allow you to track its evolution.

If you want to play with the chart click on it to go to the published spreadsheet or follow this link. [Update: you could play with the chart directly in the post but I was getting some problems with Microsoft Explorer so I decided to remove it.]

If you are not familiar with the demographic concepts, this is what you need to know to understand the chart:

Old dependency ratio: ratio between population 65+ years old / adult population (15-64 yeas old);
Young dependency ratio: ratio between population 0-14 years old / adult population (15-64 yeas old);
Total dependency ratio is the sum of the above two.

{ 3 comments }

Excel, Crystal Xcelsius, dashboards and domain names

by Jorge

Funny story… I was playing with domain names today, trying to register names for future projects like “exceldashboards.com”. Unfortunately, this is already taken. And who owns it? Business Objects, the makers of Crystal Xcelsius (remember my Xcelsius dashboard series?). Type exceldashboards.com (I refuse to add a link) and you’ll be redirected to the xcelsius home page where there’s a tag line that reads “steal the show”…

After mumbling things unpublishable, I typed “xcelsiusdashboards.com” and lo and behold, it was available! Seems that BO don’t believe in their own dashboard products…

Steal the show, they say…

{ 2 comments }

The Excel Demographic Dashboard in StarOffice/OpenOffice? Not yet.

by Jorge

I tried to create a Crystal XCelsius version of my Demographic Dashboard and failed miserably. But what about a new spreadsheet version? That should work, right? Wrong. I downloaded StarOffice 8 and just linked the pivot table (“datapilot”) to the Access population database via ODBC and waited… and waited… and waited… and finally was able to design the pivot table structure and then I waited… and waited… and waited… and then I selected a new country and I waited… and waited… and waited…

I know I must upgrade my system, but this thing is ridiculously slow. So, I’ll wait a little longer… for version 9.

{ 0 comments }

No Crystal Xcelsius dashboard, sorry

by Jorge

This is the last post in a series where I examined the possibility of creating a Crystal Xcelsius dashboard using my Demographic Dashboard as a benchmark. I’ll discuss here my overall conclusions.

I must say from the start that I am very disappointed.

Let’s start by the data set and Xcelsius integration with Excel. I struggle everyday to squeeze more and better data into my Excel files. To accomplish that, named ranges, pivot tables or some advanced Excel functions like GETPIVOTDATA are basic tools I can’t live without. Take pivot tables, for example. Crystal Xcelsius doesn’t support them and you have to use aggregate functions like SUMIF instead. But what if you can’t use them (there are many reasons, like when your source data has more than the Excel row/column limits)?

If you are serious about creating Crystal Xcelsius dashboards, I advise you to use a large data set right from the start. That will show you how crucial a good Excel data model is for the success of your dashboard. For the advanced Excel users, “a good Excel data model” probably would mean to change the way we work at a fundamental level, using less data and dropping some of the best tools from our tool set. Judging from this experiment, there is no positive cost-benefit result.

The same applies to the chart engine. Several of the tools I take for granted in Excel are missing in Crystal Xcelsius. I am trying to fight the idea that every single option I have in Excel should be available in Xcelsius, but not being able to format a series (turn on/off markets) or a specific data point, seriously undermines the overall design. For example, by adding series name to the last data point in a line chart you can remove the legend, something that, where possible, you should always do to improve chart readability. Not being able to connect data points in a scatter plot or overlap bars are other deadly sins.

Jeff, commenting in the last post, wants me to answer to this question: “Do you agree with Stephen Few’s criticisms of CX?” First of all, what criticisms we are talking about? According to Few:

There is probably some useful functionality hiding beneath Xcelsius’ video game look and feel, but the dominance of distracting visual fluff and game-like sound effects severely undermines its integrity and proclaims that Business Objects doesn’t understand or seriously care about data visualization. Business Objects does care about sales, however, and knows that many people find the video game qualities of Xcelsius entertaining. Few of our customers are experts in data visualization. They haven’t done empirical research to determine what works and what doesn’t, nor have they read the research findings of others. Vendors selling data visualization software are supposed to be the experts, working to give people what they need. It pains me to see the potential of data visualization reduced to this level. Business Objects is certainly not alone in this; just louder than most vendors in marketing this snake oil.

This accurately represents Few’s strong opinions regarding Crystal Xcelsius, I suppose. I usually agree with him, but only partially I subscribe to his opinions in this case. Let me outline some key points where I may have different views and believes:

Unlike Few (and Tufte), I don’t believe in a pure rational approach to display design. The visual communication takes place in a social context that changes the chart designer and the audience;
I believe that the chart designer must understand how people respond emotionally to his designs;
Some empirical research (pdf) in human-computer interaction suggests that “post-experimental perceptions of system usability were affected by the interface’s aesthetics and not by the actual usability of the system”; if we can transfer these results to chart design it could mean that an aesthetically pleasing chart gets a better response from the audience than a purely functional chart;
“Purely functional” is also how we can describe the use of color in Few’s dashboards; we should strive to find a point where “functional” and “aesthetically pleasing” don’t conflict but complement and add to the user’s experience;
Following Jacques Bertin, interaction builds knowledge; I believe that, when possible, at least a minimum level of interaction should be build into the visual display of information and the user should be invited to play with tool and, yes, probably we should make it entertaining;

I believe that, using Xcelsius, you can’t create an efficient visualization of even a moderately complex data set. This is a more fundamental problem than the “distracting visual fluff”. As you can see in the following pies (sorry…) you can get a simple and nicely rendered chart once you remove the visual fluff (pie #2):

Do I agree with Stephen Few? I am not far from him, but I’m taking a different path. He takes Business Objects’ sale pitches too seriously and misses Xcelsius fundamental flaws. I’m more condescending. Xcelsius is a lovely toy piano, but please don’t bring it to my concert hall…

Bottom line: if you have a small and simple data set, if you want to add that extra layer of complexity, if all you need are some rudimentary charts, if your dashboard looks clumsy in Excel, if you gain in aesthetics without compromising effectiveness, by all means, use Xcelsius. Prototype it in Excel and then compare it to what you get in Xcelsius. Be sure to have a strong data model before designing the dashboard and playing with the chart options.

For real world dashboards, use Excel or better.

{ 4 comments }

A line chart in Crystal Xcelsius: how difficult can it be?

by Jorge

Can Crystal Xcelsius replace Excel as a charting tool? As the regular readers know, I am creating an Xcelsius version of my Excel Demographic Dashboard to answer this simple question.

I am afraid the results until now are less than stellar. Although I could easily add four gauges with the four main demographic KPI, I am not really happy with the population pyramid and the barely passable scatter plot shows how limited Xcelsius’ data model is.

But today I am creating a simple line chart. Three variables and a dummy series. How difficult can it be?

This is the result. As usual, the one on the left is the original chart and the one on the right is the Crystal Xcelsius version.

The Xcelsius version is much better rendered, that’s no surprise. But let’s put that aside for a moment and focus on the legend in the Xcelsius version. Notice anything bizarre? Bizarre like in the-legend-has-nothing-to-do-with-the-chart? The legend is using markers to identify the series but wait… I turned off the “Show Markers” option… Of course you can turn on the markers, set them to size 1 and use the same color as in lines. But still…

As far as I can see, you can’t directly label the series, so you can’t remove the legend. Removing the legend should always be a priority, according to the principle of simplicity in chart design.

I used a dummy series in the Excel version to place a marker along the X axis to mark the active year. This can’t be done in Xcelsius, since you can’t turn on the “Show Markers” option for a specific series.

Finally, the X axis is not labeled because it would become unreadable with labels for 60 years. Again, there is no obvious way to remove a defined set of labels. I suppose it could be done by removing the labels in the Excel file, but I didn’t test it.

Once again, Xcelsius failed to deliver a correct copy of a simple chart. There are some open-minded Xcelsius aficionados among the readers that will kindly prove me wrong and will share with us how to solve this issues…

We are almost done with this series of posts. Next time I’ll compare table layouts, before the last post, where I’ll discuss the overall conclusions.

{ 8 comments }

Is Crystal Xcelsius a toy piano?

by Jorge

Pascal Comelade, a french musician, plays toy pianos for a living. How many of us could do the same? How many of us could (professionally) use toys instead of our standard, grown-up tools?

Now imagine that a toy maker starts marketing their toys as serious musical instruments. How would Beethoven’s 5th Symphony sound like? At first, it would be funny to see all those colorful, eye-catching instruments trying to cope with so many notes. Then the toy maker would convince us that only a small subset of notes were more than enough for the average listener, while adding more bells and whistles (literally…). One day no one would remember how the original piece sounded like.

Well, I’m starting to think that Crystal Xcelsius is a toy piano.

In this series of posts I am recreating my Excel Demographic Dashboard in Crystal Xcelsius. By using a clear benchmark I hope to provide an objective account of my findings, chart after chart. Today I am discussing the scatter plot (XY chart). Below you can see the Excel version (on the left) and the Crystal Xcelsius version (on the right).

As you can see, the Crystal Xcelsius version looks a lot better. But, is it?

If you use a large number of data points in a scatter plot some of them may overlap. Using the right size/shape combination you can minimize the impact of overlapping points (provided there is no complete overlap). In the Excel version of the dashboard I use small rings, as suggested by William Cleveland. This cannot be done in Crystal Xcelsius, so you must use a filled shape. But this is not the real problem.

I started to get error messages as soon I entered the data for the scatterplot (2 x 222 data points). Browsing the online forums (like this) I’ve found that I must be near the Crystal Xcelsius limits…

The real problem, the problem that makes Crystal Xcelsius a (misbehaving) toy, is the way it handles data from Excel. Jon Peltier tells us (in the comments) how he “always felt handcuffed by the limits of Crystal Xcelsius charts”. I feel that way too, but it gets worse when you realize that it is not only about chart options. Because it doesn’t support several key formulas, because it doesn’t like a large volume of data, because it doesn’t like calculations, because you are adding an extra layer of complexity, you must change the way you work in order to get some eye-catching charts! I know that if you try hard, using models and sub-models, tweaking here and there, you can push more data into Crystal Xcelsius, but it is not good enough, not even close.

Of course you can create pieces for toy piano. But you’ll always have to deal with its shortcomings. Like the toy piano or the pie chart, first of all you must know where the limits are, how they impact what you’re trying to communicate and decide if you can work within those limits.

{ 9 comments }

XCelsius Dashboard: the population pyramid

by Jorge

Can my Excel Demographic Dashboard be recreated in Crystal XCelsius? This is the main theme for this series of posts. In the first post I set the stage, define the rules and show how the basic “demographic KPI’s” can be displayed using gauges. The second post discusses one of the major drawbacks I find in XCelsius and similar applications: the use of textures and the consequences of it.

Back to the dashboard, I am trying to recreate the population pyramid that you can find in the Excel version. Please take a look at the Excel pyramid (above) and the one I designed in XCelsius (below):

I don’t want to discuss features and options, only the end result, but let me summarize the process:

There seems to be no easy way in XCelsius to connect data points in a scatter plot and I didn’t like the chart without them, so I had to rule out the scatter plot;
There is no option to align series in a bar chart, so it can’t be used to create a pyramid; the option is available for the stacked bar chart, but in this case you can’t display four series without stacking them (obviously);
This is a minor issue: the x-axis labels can’t be formatted to remove the minus sign but, as long as the scale doesn’t change, you can hide it with a small white rectangle.

My final solution uses a stacked bar chart to encode the current year and a subtle overlapping scatter plot to encode the reference year (1996). I could also minimize spacing between data points by using a smaller chart or by interpolating data points. I chose this one because it retains the nature of the tool (namely the texture in the bars). Feel free to send me other alternatives!

The XCelsius pyramid looks a little better than the bare bones Excel version, but spotting the differences between the reference and the current year is harder.

So, what are the learnings? Judging from this task, in XCelsius you are confined to a very small set of charts that you are unable to format the way you’d like. This is not necessarily bad. It depends on your audience, your data, what and how you want to show.

So, my advice would be: if there are no trade-offs (are you sure?), if what/how you want to say can be said in Xcelsius without significant perceptual impact, and your audience likes it, by all means, use it. But try to be very conservative in your formatting options (minimizing the impact of textures, for example), otherwise those eye-catching charts can wear out very fast.

That said, I believe that you can’t avoid trade-offs and that, by using Xcelsius, you can lose some relevant details in your data. But this is not over. Let’s see if Xcelsius proves me wrong. The next post discusses scatter plots.

{ 6 comments }

Sparklines, Excel, Crystal Xcelsius and stealth-mode charts

by Jorge

You struggle every single day to design the best, the most eye-catching chart for the next presentation. If your goal is to impress your boss, you can stop reading, I have nothing new to offer, I can’t even impress mine. But if you really want to understand your data, there is some (hopefully) interesting stuff below.

Charts 101: the visual metaphor

Charts use visual metaphors to convey abstract data. Those visual metaphors can themselves be of a more concrete or more abstract nature:

Concrete metaphors (pies, skylines) are very powerful, because users can relate to their daily life objects and experiences, so usage and meaning are easily inferred. By contrary, abstract metaphors must be learned to be decoded. You can’t find a daily life equivalent of a scatter plot.

There is a consensus that more than 5-6 data points make a pie chart unreadable, but you can plot hundreds or even thousands points in a XY chart. A sparse – dense continuum seems to follow the same direction of the concrete – abstract continuum.

From a marketing point of view, using concrete metaphors makes a lot of sense if you are selling to an undifferentiated target. This is what people know (and love?), and if the charts look familiar, pleasant and easy to understand they will want the software.

The logical next step is to be even more concrete (= more users, more sales). Enter the dashboard metaphor. Everyone knows gauges and traffic lights. The metaphor is simple (driving your company is like driving your car) yet appealing (you are in control), so let’s add the look and feel of real gauges and traffic lights. Even pie charts are no longer something that vaguely reassembles a pie anymore. They become a “real” and very polished 3D object that reflects light, like in the physical world, exactly like the ones you find in the magazines. You are entering the realm of professional design…

The role of texture

Texture is what gives these objects their real-worldness quality. It is one of the six “visual variables” (position, size, shape, value, color, orientation and texture) listed by Jacques Bertin and it has a fundamental role in scientific visualization. But scientific visualization deals with “objects” (the DNA helix, for example). If an engineer wants to know what happens when a force is applied to an object she needs texture to see how the object is changing.

Information visualization deals with abstract concepts like “market share”. You can’t point at an object and say “this is a market share”.

The problem with texture is that it requires a lot of space. Take a look at these two scatter plots from Crystal XCelsius. See that gorgeously(!) textured data points in the first one? And do you see the texture in the second chart? Look closely, I just changed the symbol size, it is still there…

If you have a complex series, with more data points, you can’t use the default symbol size. It must be much smaller. But then you are hiding the texture and the chart becomes very similar to an Excel chart. So what’s the point of using Xcelsius? Why don’t you just do it in Excel?

Eye-catching charts vs. stealth-mode charts

In a finite space (the chart area), a data value coded with a point (no dimensions) take less space than another coded with a line (one dimension) and much less than another coded with a polygon (two dimensions). Obviously.

Eye-catching charts, like the ones you get from Crystal Xcelsius and similar applications, are textured, polygon-based charts. By using a polygon to encode a data value you are cutting down the amount of data you can display in a single screen, like in a dashboard. In a data-rich environment this can be a problem and an opportunity. It can be a problem if you can’t fit the data into the available space. But it can be an opportunity for you to exercise your judgment when selecting the relevant data.

I would say that eye-catching charts are also eye-caught charts. They stay longer in the retina and they have a hard time trying to deliver their simple messages to the brain, and sometimes they fail miserably…

Stealth-mode charts, on the other hand, avoid to catch or be caught by the eye. They are smaller, use less dimensions, muted gridlines and soft colors, allowing them to pack more data. In a split second they are preattentively processed and the data is delivered to the brain even before the eye notices them (I am taking too much liberties here, preattentive processing will be more formally discussed in a forthcoming post).

Please bear in mind that showing data to your boss using stealth-mode charts is a mistake. He’ll be vaguely aware of a deeper knowledge but he will not link it to your oh-so-simple-charts, so don’t expect a promotion. Also, they make you lazy, since you (may think that you) don’t have to actively select the relevant data, and that the screen real estate is more than enough.

You can design your stealth-mode charts in Excel or in any other application that don’t use texture-based charts. Sparklines are great for stealth-mode charts but there are some issues that must be addressed (to be discussed in a forthcoming post also).

I coined the expression “stealth-mode charts” as an umbrella for a set of principles in information visualization that emphasizes the need for an alternative to the “eye-catching charts” that emphasizes insights, knowledge and higher return on the investment made in the data.

What do you think? Is there a place for stealth-mode charts in the corporate sector or are we forever stuck in all these boring eye-catching charts? Please share your thoughts in the comments.

{ 8 comments }

← Previous Entries