Untitled
Xcelsius® 2008 Dashboard Best Practices
Sams Publishing
Copyright „ 2009 by Pearson Education, Inc.
Associate Publisher
Greg Wiegand
All rights reserved. No part of this book shall be reproduced, stored in a retrieval
system, or transmitted by any means, electronic, mechanical, photocopying,
Acquisitions Editor
recording, or otherwise, without written permission from the publisher. No
Michelle Newcomb
patent liability is assumed with respect to the use of the information contained
Development Editor
herein. Although every precaution has been taken in the preparation of this book,
Todd Brakke
the publisher and author assume no responsibility for errors or omissions. Nor is
Managing Editor
any liability assumed for damages resulting from the use of the information con-
Kristy Hart
tained herein.
ISBN-13: 978-0-672-32995-1
Project Editor
Betsy Harris
ISBN-10: 0-672-32995-6
Library of Congress Cataloging-in-Publication Data
Copy Editor
Kitty Wilson
Abdulezer, Loren.
Xcelsius 2008 dashboard best practices / Loren Abdulezer.
Indexer
p. cm.
Erika Millen
ISBN 978-0-672-32995-1
Proofreader
1. Xcelsius (Computer file) 2. Dashboards (Management information systems)
Dan Knott
3. Business—Computer programs. I. Title.
Publishing Coordinator
HD30.213.A23 2009
Cindy Teeters
005.5’8—dc22
2008046377
Book Designer
Anne Jones
Printed in the United States of America
First Printing December 2008
Compositors
Nonie Ratcliff
Trademarks
Bronkella Publishing LLC
All terms mentioned in this book that are known to be trademarks or service
Business Objects
marks have been appropriately capitalized. Sams Publishing cannot attest to the
accuracy of this information. Use of a term in this book should not be regarded as
Business Objects Press
Editorial Board
affecting the validity of any trademark or service mark.
Paul Clark
Warning and Disclaimer
John McNaughton
Every effort has been made to make this book as complete and as accurate as pos-
Technical Editor
sible, but no warranty or fitness is implied. The information provided is on an “as
Javier Jimenez
is” basis. The author and the publisher shall have neither liability nor responsibil-
ity to any person or entity with respect to any loss or damages arising from the
information contained in this book.
Bulk Sales
Sams Publishing offers excellent discounts on this book when ordered in quantity
for bulk purchases or special sales. For more information, please contact
U.S. Corporate and Government Sales
1-800-382-3419
corpsales@pearsontechgroup.com
For sales outside of the U.S., please contact
International Sales
international@pearson.com
Foreword
Have you ever experienced how data can change the world? How compelling presentations
can get CEOs out of their seats? How a business dashboard can turn a regular business
manager into an invaluable hero to a company? Or how a tool that simplifies data and
makes information informative, useful, and actually fun can transform a career?
I have. It happened for me a couple years ago. My team at Business Objects was looking for
a way to present a new business opportunity with key metrics, business drivers, and trends
to our CEO. We had about an hour to present everything and help make a key business
decision.
We had a lot of slides and spreadsheets and the usual information you’d expect. But then we
summarized the entire business opportunity and how we wanted to look at the opportunity
into a single dashboard. We combined all the key data that we were using to look at the
business decision. Within a few hours, we turned mountains of tabular, virtually incompre-
hensible data into a powerful business tool. And we added the key element that every CEO
wants: the power of “what-if.” What if sales didn’t meet expectations? What if we couldn’t
meet our cost-cutting objectives? And more importantly, what if it really worked!
As we were presenting, our CEO jumped out of his chair and started to “drive” the dash-
board: He put his own experience and assumptions to the model. What we showed was that
it was actually a bad idea to make the investment that many were asking for. The numbers
didn’t add up. By not making the decision, we saved the company potentially millions of
dollars, countless resources, and a lot of churn trying to make something work that just
didn’t make sense to us or the CEO.
On the bright side, by taking advantage of the power of Xcelsius to build the dashboard, my
team gained broad recognition for providing something that was simple enough, com-
pelling enough, and actually fun enough to change the course of the business, and in the
process, our careers. Having the right skills to build a business dashboard without over-
whelming the audience made all the difference.
Sure, we could have shown some data in a spreadsheet, but the only reason CEOs get out
of their chairs to look at a spreadsheet is because the font is too small to read. Put that data
into Xcelsius and you will quickly change the way you see and monitor your business and
completely change the decision-making experience.
Our challenge to you, the reader, is to go beyond the creation of a simple chart or graphic
you’ve built in a presentation or spreadsheet tool to a real business dashboard that can have
a major impact on your most important business aspects. Model your business. See how
decisions impact the environment. See how pricing, promotions, and marketing impact
consumer behavior. Liven up your dashboards by connecting your dashboard to real-time,
live data from within your company or any web service to get up-to-the-second monitoring.
Use your business data anywhere, and take your proposal from the shop floor all the way to
the board room.
xii
Xcelsius® 2008 Dashboard Best Practices
In my experience, the key elements that you need to effectively build business dashboards
that will make a difference include the following:
■ Designing a spreadsheet that clearly identifies the inputs and results for use by the
dashboard. Of course, let the spreadsheet do the hard work of crunching the complex
calculations!
■ Mapping visual components on the dashboard to drive the inputs on the spreadsheet.
You have a wealth of components to choose from—everything from sliders to dials and
list boxes, and then some. You can also manage their properties so that they are truly
interactive.
■ Adding visual components to display spreadsheet results. Again, you have a wealth of
components to choose from. Every dial, gauge, chart, map, and table can be imbued
with complex properties and alerts.
■ Building dashboard interactivity and managing visibility of the various components so
that your dashboard keeps toe to toe with its embedded spreadsheet.
Loren’s book shows how to take advantage of these features in Xcelsius 2008 to build
astounding dashboards. Make sure you take some time to learn some best practices laid out
in this book. Try out the samples to get you started. And make sure you share your great
work with others.
Every day we see more great dashboards that are changing the world, one dashboard at a
time. And remember, just as a good dashboard can improve you and your company’s per-
formance, bad data, poor layout, excessive use of unnecessary bells and whistles, and irrele-
vant data and information can have the opposite effect.
Good luck. Have fun. And don’t be surprised when you get a welcome, yet unexpected,
reaction when you share your dashboards. Done right, you’ve truly never seen a spread-
sheet do this before!
James Thomas
Vice President, Product Management
Business Objects, an SAP company
Preface
A broad and growing community of professionals regularly prepares or needs to prepare
dashboards and interactive visualizations and reports. Like many of those other profession-
als, I have used Excel to create useful reports and dashboards. The problem is that unless I
incorporated extensive amounts of one-off code, Excel lacked some essential features that I
was looking for:
■ The ability to design a dashboard interface by dragging and dropping components on a
canvas
■ The ability to map visual components to a “live” spreadsheet built using my Excel
models
■ The ability to deploy simple, self-contained dashboards that are suited for visual data
analysis by ordinary users
Those capabilities existed in Xcelsius 3.0. Two product generations later, Xcelsius 2008 has
undergone a metamorphosis; Xcelsius now includes a well-honed and highly integrated
spreadsheet and dashboard design environment, significantly greater spreadsheet functional-
ity, more visual components and interface options, a revamped and expanded framework for
data connectivity, and the ability to create entirely new custom-designed components on
equal footing with built-in components.
This is great stuff. It sounds like everybody ought to be using Xcelsius 2008, for anything
and everything. But Xcelsius 2008 isn’t intended to be a jack-of-all-trades. First and fore-
most, Xcelsius 2008 is a serious tool for building interactive dashboards and intelligent visu-
alizations. The secret to its power is how it is joined at the hip with spreadsheets.
Xcelsius 2008 is remarkably easy to use. From a dashboard layout perspective, everything is
point and click. You don’t need much in the way of spreadsheet prowess to start doing inter-
esting and useful things with Xcelsius. This quick bang for the buck is like kindling wood in
a furnace: It’s enough to get a flame started, but it won’t heat up the room. To get a roaring
and self-sustaining fire, you need to take things to the next level.
So what is stopping you from building better dashboards? The biggest challenge holding
most people back is lack of time. If you are busy worrying about monitoring and meeting
production quotas, or allocating budgets among competing projects, you are probably not
going to spend a lot of time improving on a dashboard design once you get it working.
Maybe for an occasional dashboard, that’s smart thinking. If your dashboard serves you well,
you will no doubt use it to do more things. Who knows? Maybe you need to enable weekly
or daily analysis in addition to monthly analysis.
Say that you want to add a second product line, monitored by a dashboard. You start with
your already working dashboard design as your template and add more features. As you
keep cloning, you are stepping up your maintenance responsibilities and possibly bloating
your dashboard. At some point not far down the road, the dashboard capabilities plateau. It
is not nearly agile enough to keep up with changing requirements or expectations. This is
where best practices come into play. I know that time is premium for you. It is for every-
one. To save you valuable time, I have worked out a wealth of best practices and techniques
so that you don’t need to reinvent the wheel.
In this book, I do a few other things:
■ Introduce you to the features you need to know. I get you started with setting up your
Xcelsius workspace. I introduce you to essential components and show you how to use
them. I help you build up your spreadsheet skills in an Xcelsius-centric way.
■ Show you how to use the new and important features of Xcelsius 2008 so that you can
quickly transition to this newer technology.
■ Cover the essential components you will regularly be using in dashboards, from charts
to dials, gauges, sliders, and maps. I cover the standard features such as drill down and
alerts.
■ Show how to turbo-charge the various dashboard components so that they do things
you wouldn’t ordinarily expect. For instance, you’ll learn how to use a single dial on a
dashboard to set the values of dozens or hundreds of variables.
■ Show how to design simple and effective dashboard interfaces. When these need to be
scaled up to do complex things, the designs don’t change, and they don’t break down.
■ Describe how the preparation and processing of data, including techniques for validat-
ing and structuring of data, play a central role in dashboard best practices.
■ Devote whole chapters to constructing spreadsheet formulas embedded in dashboards,
statistical analysis, financial analysis, and working with less–than-optimal data.
■ Show how to utilize features of Xcelsius 2008 for remote data connectivity, such as
XML maps and Web Services.
■ Explain how to construct custom components.
The undercurrent that runs through this book is empowerment. Every step of the way, I
show how you can work smarter by using best practices.
Loren Abdulezer
December 2008
INTRODUCTION
In this introduction
Getting What You Need from This Book
3
What This Book Covers
5
Conventions Used in This Book
7
2
Introduction
One of the first things that caught my attention about Xcelsius is that it is fundamentally a
paradigm-shifting technology. Xcelsius intentionally blurs the distinction between spread-
sheets and presentation-layer dashboards.
Back in 2005, when the product first started getting traction, it was ahead of its time. Most of
all, Xcelsius needed to catch up to its own revolutionary ideas. Back then, the spreadsheet
portion and the canvas lived in parallel but disjointed universes. They could be tethered by
statically linking component properties to fixed cell coordinates. This was a great proof of
concept and prototype but wasn’t always practical.
From time to time, spreadsheet models do need to get updated. With early versions of
Xcelsius, if you inserted or deleted a row or column, it sometimes completely derailed the
Xcelsius component-to-spreadsheet-cell mapping. At the cost of increased spreadsheet design
complexity, I developed a solution to this problem. This opened the floodgates so that I
could redesign imported spreadsheets with impunity and not have to worry about upsetting
the mapping between components and spreadsheets. In effect, it gave me a three-year head
start in developing effective techniques and best practices that are applicable to Xcelsius
2008.
Xcelsius 2008 supports some new spreadsheet functions. One of them is an amazing and
often-overlooked function called OFFSET. In earlier versions of Xcelsius, you could use INDEX
to redirect data from any column or row of your choosing and pipe it into a chart or other
visual component in your dashboards—a technique I call context switching. Thanks to OFFSET,
this functionality can now be truly turbocharged using Xcelsius 2008.
Over the years I developed a wealth of techniques and best practices so that the data and
computational side of the Xcelsius dashboards could be on par with the stunning visualiza-
tions so often associated with Xcelsius.
As Xcelsius 2008 moved past the drawing board, I reworked and substantially extended those
techniques for the new Xcelsius. I often found myself more on the bleeding edge than cut-
ting edge of this new technology.
After Service Pack 1 for Xcelsius 2008 solidified, the techniques, methodologies, and
approaches to dashboard design with Xcelsius 2008 all fell into place rather naturally.
Xcelsius 2008 as a product and technology has caught up with its revolutionary ideas and is
ready for primetime. That is half the battle. The task ahead of you is to use Xcelsius 2008 to
catapult your dashboards and visualizations beyond toy demonstrations.
Using Xcelsius is easy when you understand the ins and outs of building and designing dash-
boards.
The first lesson you are going to learn in this book is that if you really want your Xcelsius
dashboard to shine, you have to take responsibility for managing the data that feeds into your
visualizations. This means you have to control the data, juggle it with formulas, and do what
is necessary so that it is on rock-solid footing by the time it appears visually.
Getting What You Need from This Book
3
Why is all this really necessary? Dashboards are interactive, and Xcelsius dashboards per-
form live computations. The data in a dashboard is generally not static and is subject to
ongoing updates and revisions. To complicate matters, the drivers and inputs in visualiza-
tions are subject to the whims of your dashboard users.
There are things you can do to keep your dashboards out of hot water. For example, say that
you have three companies vying for market share—your company and two competitors. You
know that 100% of the market share will never be exceeded. How can you build a dashboard
to assess your competitive positioning, assign market share, and never have to worry about
overallocating percentages? Best practices provide a structured methodology for dealing
with issues of this kind and for taking what might be inefficient or unmanageable and keep-
ing the process sane and contained.
N O T E
You can find a dashboard of this kind in Chapter 2, “Showcase of Xcelsius 2008
Dashboards,” and its implementation is covered in Chapter 12, “Smart Data and Alerts.”
Getting What You Need from This Book
My goal in this book is to help you quickly learn specific techniques and practices, provide
information in a sensible order, and help you understand some practical matters about work-
ing with examples and your own files.
Locating Specific Techniques Quickly
This book covers a broad range of topics in 16 chapters. The chapters are chock full of valu-
able techniques, tips, and strategies. The chapters are organized by subject matter rather than
by best practice. To help you locate best practices, I’ve cataloged more than 100 best practice
techniques and tips in Appendix C, “Xcelsius Best Practice Techniques and Hip Pocket
Tips,” which lists topics and where to find them in the book. To help you locate the details
within the chapters, a best practices icon appears in the margin next to each best practice.
N O T E
You can find the dashboard files that accompany this book on www.XcelsiusBestPractices.
com.
How to Read This Book
This book will be valuable to you whether you are new to Xcelsius or already have some
experience under your belt. You may want to approach the text differently, depending on
your familiarity with creating dashboards and using Xcelsius.
4
Introduction
For New Xcelsius Users
If you are entirely new to Xcelsius, first skim Chapter 2, which quickly introduces you to
various kinds of Xcelsius dashboards. Then read through Chapter 1, “Motivation for Using
Xcelsius 2008,” so that you have an idea of how your spreadsheets and dashboards work
together in Xcelsius. Move on to Chapter 3, “Getting Familiar with Xcelsius 2008,” to get a
foundation for working with Xcelsius. Think of Chapter 3 as your first day of on-the-job
training.
If you want, you can skim through Chapter 4, “Embedded Spreadsheets: The Secret Sauce
of Xcelsius 2008,” but you may be better off immediately jumping to Chapters 5, “Using
Charts and Graphs to Represent Data,” and 6, “Single Value Components: Dials, Gauges,
Speedometers, and the Like.” Don’t worry about the details; concentrate on the basics. Now
you can jump to pretty much any other chapter in the book. As you need, refer to Chapter 4
when the spreadsheet stuff gets too heady.
For Veteran Dashboard Designers with Prior Xcelsius Experience
If you are a veteran user of Crystal Xcelsius, you can initially concentrate on Chapter 3 to set
up your Xcelsius 2008 environment. The Xcelsius 2008 workspace may take some getting
used to, but you will definitely find the tight Xcelsius/Excel integration to be liberating.
Then spend some time reading through Chapter 4 but don’t worry about reading it from
beginning to end. Instead, pick a spreadsheet topic of interest to you—the text functions or
date and time functions, for example. Read through that section thoroughly to learn how to
set up and use those functions in a dashboard setting.
Make sure you go through Chapters 5 and 6, which describe valuable dashboard designs and
constructions. They may give you ideas on how to redesign some of your own. From there
on, feel free to read any of the chapters in any order you please.
Accessing Legacy Xcelsius Files
Xcelsius 2008 can read and convert Crystal Xcelsius files (that is, Xcelsius version 4.5). If you
have files built with Xcelsius 4.0 or earlier, you will first need to open them by using Crystal
Xcelsius and save them as Xcelsius 4.5 files. Then you may be able to open them by using
Xcelsius 2008.
The process of converting legacy files to Xcelsius 2008 may not be so easy. Instead of trying
to retrofit a legacy Xcelsius file, you may be better off building a new dashboard from
scratch, using the best practices described in this book.
Best Practices Versus Shortcuts
Some quick solutions to vexing problems cut corners and work, and some are hacks. For
example, the common practice of overlaying charts one on top of another can work and can
be effective, and with the previous versions of Xcelsius, it may have been the only way to do
certain things. I do not view such strategies as best practices, so I generally steer away from
What This Book Covers
5
talking about practices of this kind. However, I do make some exceptions, discussing such
techniques and explicitly citing them as not being best practices but being practical short-
cuts. For example, Chapter 14, “Other Dashboard Techniques and Practices,” discusses a
filled radar chart with alerts. I point out in the chapter that this is not a best practice. I also
list this as an item in Appendix C.
In summary, while I try to keep the discussions pure and focused on best practices, I balance
this with practical techniques.
What This Book Covers
The purpose of Part I, “Xcelsius 2008 Fundamentals,” is to color your impressions about
Xcelsius 2008 and Xcelsius dashboards and lay a foundation for how to approach the use of
Xcelsius 2008.
Chapter 1, “Motivation for Using Xcelsius 2008,” gives you a backdrop for Xcelsius and
spreadsheets and reveals how the two are heavily intertwined.
Chapter 2, “Showcase of Xcelsius 2008 Dashboards,” is like a wine tasting party. You’ll get a
sampling of different kinds of dashboards and have a chance to think about what’s important
for you and what might be interesting to pursue when you start building dashboards. In this
chapter, I point out where you can find more about specific dashboards or dashboard fea-
tures.
Chapter 3, “Getting Familiar with Xcelsius 2008,” helps you set up your Xcelsius workspace
so you can quickly begin to build rudimentary dashboards. It also sets the stage for building
full-featured dashboards. It’s important that you think clearly about using the features of the
Designer environment and tapping into the unique dashboard capabilities for which Xcelsius
is known.
The powerhouse behind Xcelsius is the underlying spreadsheet. If you want to do really
powerful and astounding things with Xcelsius, you need to know how to use spreadsheet for-
mulas and functions. Chapter 4, “Embedded Spreadsheets: The Secret Sauce of Xcelsius
2008,” is a skill-building chapter that works from the ground up on how to construct spread-
sheet formulas in Xcelsius. This chapter is a comprehensive reference guide that covers
essential spreadsheet functions, replete with examples and pragmatic constructions, and
identifies some important differences between Excel functions and their handling within
Xcelsius.
Chapter 5, “Using Charts and Graphs to Represent Data,” introduces you to charting com-
ponents and how to use them. You’ll learn about the major kinds of charts and how to work
with them; you’ll end up with a better understanding of practical matters such as chart scal-
ing. You’ll learn about some new kinds of components introduced in Xcelsius 2008, such as
Tree Map components. I also show some techniques for handling known charting problems,
such as displaying negative values in bubble charts.
6
Introduction
Chapter 6, “Single Value Components: Dials, Gauges, Speedometers, and the Like,” pro-
vides information on sliders, dials, and gauges. In this chapter, I cover Xcelsius themes to
show how you can alter the appearance of your dashboards. A common criticism of dash-
board dials and gauges is that they consume large swaths of screen space. I show a technique
that allows you to share a single component, such as a dial, with a virtually limitless number
of dashboard variables.
Part II, “Xcelsius 2008 Best Practices and Techniques,” is a comprehensive guide to Xcelsius
components and best practice techniques.
When you discover that you can pack a lot of punch in your dashboards with the wide array
of components, you’ll find your visualizations becoming quickly crowded. Chapter 7, “Using
Multi-Layer Visibility in Your Dashboards and Visualizations,” shows you how to avoid
needless complexity by using best practice techniques for managing visibility.
Chapter 8, “Managing Interactivity,” gives you more control in managing interactivity. You’ll
learn about putting to use drill down features and interacting with various kinds of inter-
faces.
Chapter 9, “Xcelsius and Statistics,” is an Xcelsius-centric foray into statistics and statistical
analysis. This chapter shows how to meaningfully integrate statistical techniques with
Xcelsius. For example, histograms are commonplace, but is it commonplace to have a his-
togram where you can use a slider to set the boundaries between categories appearing in the
histogram?
Chapter 10, “Financial Analysis,” shows how you can use the power of an underlying spread-
sheet to build dashboards for converting between accrual and cash accounting, Value at Risk
dashboards, and ratio analysis.
Chapter 11, “Maps in Xcelsius,” shows you best practices on using Xcelsius Map compo-
nents for traditional and nontraditional applications, including augmented maps, colorized
maps, multi-selection maps, international maps, and connected maps.
Chapter 12, “Smart Data and Alerts,” shows you how to utilize alerts in Xcelsius and how to
create smart data.
Chapter 13, “Working with Less-Than-Optimal Data,” shows practical spreadsheet tech-
niques for detecting the presence of transposed digits, dealing with raw and unclean data,
working with rounding and truncation errors, fixing faulty formulas, and dealing with scaling
issues.
Chapter 14, “Other Dashboard Techniques and Practices,” addresses a variety of dashboard
design solutions, including filled radar charts with alerts and avoiding occlusion with area
chars. This chapter introduces a dashboard technique I call an “ABC” chart that flows two
data sources into a combined chart. This chapter also addresses an interesting technique
involving box plots.
Conventions Used in This Book
7
Part III, “Advanced Features,” introduces advanced features of Xcelsius 2008.
Chapter 15, “XML and Data Connectivity,” explains how the Xcelsius Data Manager unifies
data connectivity and outlines basic techniques for defining data connections and managing
data refreshing. These principles are illustrated with XML Map components and Web
Services.
Chapter 16, “Creating Custom Components for Fun and Profit,” opens the door to doing
something with Xcelsius not previously possible—constructing custom components. In this
chapter, I spell out the essential setup needed for designing custom components, outline the
workflow involved in building components, illustrate how to build a component, and outline
important and expanded next steps.
This book includes three helpful appendixes.
Appendix A, “Supported Spreadsheet Functions in Xcelsius 2008,” outlines the full range
of Excel spreadsheet functions that are supported in Xcelsius 2008. This complements
Chapter 4.
Appendix B, “Xcelsius Product Family Comparison,” outlines the differences among the var-
ious editions of Xcelsius 2008: Xcelsius Present, Engage, Engage Server, and Enterprise.
This appendix describes each edition’s general features, built-in assistance, font support, data
connectivity support, export and snapshot options, themes and styles, and back-end server
support. I also run through a comprehensive list of component types, organized by Chart
components, Contain components, Single Value components, Selector components, Map
components, Art & Background components, Text components, Web Connectivity compo-
nents, and Other components (such as the Interactive Calendar, Trend Analyzer, and other
miscellaneous components).
Appendix C, “Xcelsius Best Practice Techniques and Hip Pocket Tips,” is a guide that helps
you immediately locate specific best practice techniques. It identifies valuable techniques and
where to find each in the book.
Conventions Used in This Book
This book uses several special elements:
T I P
Tips provide advice or describe a different way of accomplishing a task.
N O T E
Notes present extra information about a topic.
C A U T I O N
Cautions pull out critical information about fixing or avoiding problems.
8
Introduction
Code-continuation arrows are used when a line of code won’t fit on one printed line. The
code is wrapped to the next line and the continuation is preceded with a code-continuation
arrow, like this:
-(B7*(EXP(B8*C12*(-1)))*(NORMDIST(((LN(B6/B7))+((B8+(B13*B13/2))*C12))/(B13
➥*(SQRT(C12)))-(B13*SQRT(C12)),0,1,TRUE)))
CHAPTER
5
Using Charts and Graphs to
Represent Data
In this chapter
I
Choosing the Right Components for a Dashboard
128
Building on Your Visualizations
132
Viewing Grouped Data with Stacked Charts
135
Working with XY and Bubble Charts
141
Working with Tree Maps
148
Issues and Techniques Related to Scaling
150
Putting Visual Data Analysis into Focus
154
Closing Thoughts
154
128
Chapter 5 Using Charts and Graphs to Represent Data
One of the reasons dashboards are popular and successful is that they help you show infor-
mation and relationships that would otherwise be difficult to see. It should come as no
surprise that charts and graphs play a key—or even central—role in the preparation of
dashboards.
The goal of this chapter is to introduce you to using charting components in Xcelsius 2008.
While many of the features of charting or graphing components are outlined, the emphasis
is on how to use charting and graphing components to better convey information.
Sometimes, important information is buried in the rows and columns of data. Visualizing
data the right way can help to reveal insights.
Choosing the Right Components for a
Dashboard
Every dashboard has a story to tell. As you create a dashboard, your choice of components
and the way you set your chart attributes can either bring out that story or bury it.
Say that you want to incorporate the following snippet of data, which represents the number
of daily visitors to a website, in your dashboard:
Date Visitors Date Visitors
1/1/2009 4667 1/12/2009 4648
1/2/2009 4349 1/13/2009 5154
1/3/2009 3678 1/14/2009 5281
1/4/2009 3094 1/15/2009 5088
1/5/2009 4326 1/16/2009 4709
1/6/2009 4627 1/17/2009 3477
1/7/2009 4615 1/18/2009 3078
1/8/2009 4743 1/19/2009 4617
1/9/2009 4888 1/20/2009 5357
5
1/10/2009 3321 1/21/2009 5421
1/11/2009 2955 1/22/2009 4902
What is printed here is just a snippet. The full data can easily span a year or more. You can
find the full spreadsheet for this example in the file ch05_SampleData.xls.
Your first hurdle is to determine how much data you want to display. Figure 5.1 shows two
alternative views of the data. The top-left graph represents a 22-day snippet of data. The
lower-right graph represents data over the full range of dates, roughly spanning a full year.
A quick glance reveals an increasing progression in the data over time, but there is a lot of
variation throughout the course of a week. It would be nice to be able to choose an arbitrary
point in the timeline and show all the data over, say, a 30-day period. You can do this by
using the OFFSET function (see Figure 5.2 or the ch05_SampleDataEnhanced.xls file).
Choosing the Right Components for a Dashboard
129
Figure 5.1
Two ways to repre-
sent the data on a
dashboard.
Figure 5.2
OFFSET allows you to
choose data from any
point in the timeline.
5
It’s not difficult to turn this into a rudimentary dashboard (see Figure 5.3).
Here are some quick steps to take:
1. Launch Xcelsius 2008 and import the ch05_SampleDataEnhanced.xlf file.
2. Drag a Column Chart component onto the canvas. If you prefer, you can instead use a
Line Chart component or a Combination Chart component.
130
Chapter 5 Using Charts and Graphs to Represent Data
3. Map the component to the underlying spreadsheet data.
Click the chart and in the General tab of the chart’s properties panel, click the By Series
radio button and then click the + button to add a data series.
Within the added data series, link the Y values to the data to be displayed. If you are
following this example, this would be cells H7:H37 of the SourceData worksheet.
If for some reason you want the Y-axis to appear on the right side of the chart, choose
Secondary Axis instead of Primary Axis.
Link the category labels to the cells G7:G37.
4. Drag a Spinner component onto the canvas and link the data field to the day number
(in this example, it is cell H1).
Figure 5.3
A very basic timeline
dashboard.
5
When you open the dashboard in preview mode or export the dashboard, you should be
able to move along the timeline by clicking the up and down arrows in the Spinner control
or jump to a day number by typing in the number.
There are a number of things you need to fix in the dashboard you’ve created so far. As you
use the Spinner control to advance in the timeline, the column chart is a bit jittery between
clicks. This is because the data animation feature of the Column Chart component is
enabled. In general, data animation is a good thing; however, when you are trying to incre-
mentally advance along a timeline, this feature becomes distracting.
In your Xcelsius workspace, select the Column Chart component. In the Behaviors tab of its
properties panel, click the Animations and Effects tab and deselect Enable Data Animation
(see Figure 5.4).
Choosing the Right Components for a Dashboard
131
Figure 5.4
Turning off data
animation can
remove the “jitters”
from a chart.
Eliminating the jitters is easy enough. Unfortunately, the Spinner component still doesn’t
behave exactly as you need it to because you haven’t given it minimum and maximum limits.
Unless you specify otherwise, the Spinner component automatically defaults to a minimum
of 0 and a maximum of 100. In this example, you should set the minimum to 1 instead of 0.
The maximum limit should be a number well above 100. You have a choice of typing your
own value or linking to some value on the underlying spreadsheet.
The Column Chart component in Figure 5.4 is set to display 31 vertical bars. They appear a
little cramped. You can improve the appearance by setting the marker size for the data series
to a value smaller than its default of 17 (see Figure 5.5).
Figure 5.5
Adjusting the vertical
bar width in a column
chart.
5
N O T E
Xcelsius 2008 allows you to specify a theme such as Nova, Halo, Elan, or Microsoft
Classic. If you plan on trying out different Xcelsius themes on your dashboard, I strongly
recommend that you do so before making custom formatting changes to your canvas
components.
132
Chapter 5 Using Charts and Graphs to Represent Data
Building on Your Visualizations
The Spinner component is not the only component that’s suitable for setting a point in a
timeline. You could instead use Slider, Dial, or Calendar components, to name a few.
The data becomes more interesting when it is not set in a vacuum. You might, for instance,
want to compare the number of unique daily visitors to a website to the number of page
views. To do this, you would need to add a second data series to the chart. Because compar-
ing unique visitors and page views is really like comparing apples and oranges, a column or
bar chart is not suited for this task, even if they were both plotted over the same range of
dates. Line charts and combination charts work better for this purpose. As long as you don’t
need to make use of the Xcelsius Alerts feature, the Combination Chart component is the
best choice in this situation.
Putting Your Data onto a Timeline
Figure 5.6 shows how a combination chart can be used to present two data series: The verti-
cal bars represent visitor count, and the line graph represents page views. Because visitor
count and page views are not exactly the same kind of quantity, you need to make use of a
dual-axis facility.
Figure 5.6
A combination chart
is well suited for
simultaneously dis-
playing different kinds
of information along
a common axis.
5
The following are important design features of this combination chart:
■ You can make the chart title and/or subtitle dependent on the underlying spreadsheet
content. In this example, the subtitle is pegged to cell C5, which changes every time the
day number in the Spinner control is changed.
Building on Your Visualizations
133
■ The Spinner control title and rectangular background are purposely similar in appear-
ance to the Combination Chart legend. This allows the dashboard user to perceive the
Spinner control as an actual part of the combination chart.
■ The plot area of the chart is a distinctly different color or shading than the area imme-
diately behind the chart. This helps the visual data to stand out. The horizontal grid-
lines are visible, but they don’t compete for attention with the chart data. In particular,
only the major gridlines are enabled. If minor gridlines were enabled, the chart might
be a little too busy.
■ The labels along the axes and in the legend appear in boldface, making the chart easier
to read. Using contrasting colors or shades between the chart labels and their back-
ground also helps the readability.
There are some hidden wrinkles that you need to be aware of related to combination charts.
Figure 5.6 shows one of them. The primary axis ranges from a value of 1000 (a nonzero
number) to 6000. The secondary axis ranges from a value of 0 to 120K. As you cycle through
the days, as shown in Figure 5.7, notice that the scaling is not exactly proportional.
Figure 5.7
The scaling in this
chart is not always
proportional.
5
On day 147, the maximum value of both axes jumps up 50% (from 6000 to 9000 and from
120K to 180K), but the minimum values do not change uniformly. The primary axis originally
starts at 1000, and on day 147, it grows to 3000. The secondary axis originally starts at 0, and
it remains unchanged when the timeline advances to day 147. Clearly, the scales do not
remain proportional as you advance the timeline.
You can force these scales to be proportional, but to do so, you must have complete control
over the scaling, and you may not always be happy with the chart appearance. You can
experiment with the file ch05_DataViewer.xlf, which provides a solution.
You may need to be aware of a couple other things. Dual-axis charts are generally supported
in Xcelsius 2008. If you plan on displaying three or more data series in a chart, at least two
of the series will have to share either the primary axis or the secondary axis. If your data
series contains similarly valued items (such as percentage of efficiency or market penetra-
tion), this would not be a problem. If the values between data series vary significantly, this
134
Chapter 5 Using Charts and Graphs to Represent Data
could be problematic. Consider the example of unique visitor counts and total page views. If
you want to plot the ratio of page views per visitor, you might find numbers typically varying
between 10 and 25. When you try including these as an additional data series in the combi-
nation chart, the data becomes flatlined, as the numbers are too small for either of the pri-
mary or secondary scales. To cope with this issue, you have several strategies available.
■ You could put the page views on the same axis as the visitors and place the page views
per visitor on the other axis. Unless the data series sharing a common axis have similar
values, this is not going to be a very effective solution. In this particular case, the page
views dominate. The visitor count is visible but too small, resulting in loss of meaning-
ful information.
A common technique for dealing with quantities that are vastly different in order of
magnitude is to apply logarithmic scaling instead of linear scaling.
■ You could apply context switching so that only one data series is displayed at any time,
but the user would have complete freedom to choose which two data series you want
to view.
■ You could overlay a line chart on top of the combination chart. The line chart would
need to be precisely positioned. Its background would have to be disabled so it is fully
transparent. You would not display the line chart axis labels. The line chart axes could
be hidden as well.
■ Instead of overlaying a chart, you could make a separate chart that is pegged to the
same timeline as the main chart. If you are going to follow this strategy, and the time-
line shifts the displayed data to the left or right, you should place the separate chart
directly below or above the main chart, not to its left or right (see Figure 5.8).
Figure 5.8
5
A possible layout for
two charts on the
same timeline.
Viewing Grouped Data with Stacked Charts
135
T I P
In order to create a unified appearance when displaying more than one chart in a dash-
board, you can enclose the charts in a single rectangular shaded region, as is done in
Figure 5.8.
Viewing Grouped Data with Stacked Charts
Stacked charts—whether column, bar, or area charts—have features similar to their
unstacked counterparts. An obvious difference is that the data displayed in a stacked chart is
shown cumulatively.
With stacked charts, you can set the transparency of the data series. The transparency slider
shown in Figure 5.9 applies to all the data series. The series cannot be individually set.
Figure 5.9
Adjusting the trans-
parency for your
data series.
5
Transparency plays a more important role with a regular area chart than it does with a
stacked chart because valuable data can be easily obscured with a regular area chart (see the
lower-left corner of Figure 5.10).
Xcelsius 2008 has the Stacked Area Chart component, but there is no option to automati-
cally represent data based on its relative contribution, as shown in the top-right corner of
Figure 5.10. To do this, you need to prepare your spreadsheet data so that the data is repre-
sented in terms of its relative contribution. Mathematically, this is straightforward. In the
current example, you simply divide each of the values for the department by the total quan-
tity for the quarter. Because the quantities for the quarters add up to 100%, the maximum
limit for all the quarters is the fixed value 1. This is what gives this kind of stacked area
chart a horizontal plateau.
136
Chapter 5 Using Charts and Graphs to Represent Data
Figure 5.10
Various ways to rep-
resent data by using
the Area Chart and
Stacked Area Chart
components.
You still need to make a further adjustment to the scaling for your Y-axis. Use of auto-
scaling will push the maximum value on the Y-axis to a number greater than 1, to something
like 1.2. To regain control, you need to set your scale to manual and peg the minimum and
maximum values to 0 and 1, respectively (see Figure 5.11). In addition, you have the choice
of setting the number of divisions along the Y-axis or the size of the division. Both of these
approaches are equally suited because your scale is fixed.
Figure 5.11
Setting the chart
5
scaling for displaying
relative contribution.
Viewing Grouped Data with Stacked Charts
137
Avoiding Needless Data Series Congestion
One of the challenges of presenting information in a dashboard setting is that graphical dis-
plays can easily get overcrowded with data. The List Builder component allows a dashboard
user to cope with this situation by enabling him or her to select which data series to display
and in which order.
The Stacked Area Chart component is useful, but it is even more useful when combined
with other components, such as the List Builder component (see Figure 5.12).
Figure 5.12
A List Builder compo-
nent lets you choose
which data series to
plot on a display.
In the bottom-right corner of Figure 5.12, the data is displayed in tabular form, using the
5
List View component. List View components have several benefits:
■ They are scrollable.
■ The column widths are individually adjustable.
■ You can sort the data on any column by clicking the appropriate column header. You
can toggle between sorting in ascending order and sorting in descending order.
List Builder components are great for when you want to cherry-pick certain pieces of infor-
mation. Say, for instance, that you want to compare the sales performance of two managers.
C A U T I O N
There is one thing you need to consider if you are thinking about using a List Builder
component. This component works by copying values, so the values displayed in the des-
tination cells are “frozen” when the List Builder component update button is pressed.
138
Chapter 5 Using Charts and Graphs to Represent Data
There are circumstances in which you may want to see the totality of all the data but want
to lump the smaller data values into one big group. You might, for example, be analyzing
sales and want to see the detail for your four or five biggest customers and also see the com-
bined total of all the remaining customers. Having a slider to magically set the dividing line
between showing details and grouping the remainder would be very convenient.
Dynamically grouping or lumping data is especially important when it comes to Pie Chart
components. Figure 5.13 shows an example of this.
Figure 5.13
Dynamic data group-
ing lets you control
how much detail you
want to see.
5
The framework for implementing dynamic data grouping is straightforward. In your under-
lying spreadsheet, start by having your data sorted from largest to smallest (see column C in
Figure 5.14).
Place an input cell (see cell C4 in Figure 5.14) whose value is set by a slider or some other
selector-style component, such as a Dial or Spinner control.
Calculate the total amount of sales or whatever you are displaying for the top-tier customers
or items displayed in your Pie Chart component (see cell C2 in Figure 5.14). This is based
on the value in the input cell, as set by your slider- or selector-style component. In this
example, subtract the top-tier sales from the total sales to get the sales for “all others.”
Populate a portion of the spreadsheet (such as columns F and G) with information needed
for the Pie Chart component. You can use a formula like this:
=IF(A10<=$C$4,C10,””)
where cell C4 is the location of the input cell that is set by the slider.
Viewing Grouped Data with Stacked Charts
139
Figure 5.14
Populate columns F
and G with only the
data needed based
on the input cell.
N O T E
Remember to set your Pie Chart component’s behavior properties to ignore blank values.
Drilling Down with Pie Charts
What good is having lots of data if you can’t get to the underlying details? The quantity of
sales in the previous example may be annual sales, which is composed of monthly data. It
would be convenient to examine the breakdown of sales on a month-by-month basis. This is
accomplished using the drill down feature that is built into many of the Xcelsius 2008 visual
components.
5
In your Pie Chart component’s properties panel, go to the Drill Down subtab of the
Behaviors tab. Click the Enable Drill Down check box (see Figure 5.15). You need to specify
whether you want to drill down based on position, value, row, column, or status list. You
need to specify a destination range and, depending on the type of drill down, a source range.
For this example, you want to choose the position—that is, which slice of the pie you want
to examine—so it is not necessary to specify the source range.
You also need to tell Xcelsius whether you want to drill down whenever the mouse passes
over a slice in the Pie Chart component or when the slice is clicked.
Because you only need to find out which slice is selected for drill down, the destination
range is a single cell, namely the position. It would be a good idea to set the location for this
nearby the input cell set by the slider (in this example, cell C5).
To get the drill down data, it’s just a matter of extracting the particular row from the
monthly data based on the input cell (C5 in this example). You could display the retrieved
data on a Bar Chart component.
140
Chapter 5 Using Charts and Graphs to Represent Data
Figure 5.15
Setting drill down
options.
There’s just one problem: If your tabular data follows a left-to-right chronologic sequence
(such as January, February, March, and so on), the bar chart displays the most recent month
at the top. The result is a sequence of dates that reads downward as December, November,
October, and so on. To have the bar chart show a January, February, March, and so on
sequence, you need to reverse the retrieved data (see Figure 5.16).
Figure 5.16
5
Notice that the
extracted data needs
to go from right
to left.
When this is corrected, the dashboard renders as expected (see Figure 5.17).
As a little extra added touch, you can create a miniature isolated slice, as shown in the
upper-right inset of the bar chart in Figure 5.18. This helps provide feedback on what slice
of the pie chart is being revealed in detail within the bar chart.
Working with XY and Bubble Charts
141
Figure 5.17
A Pie Chart compo-
nent with drill down
to the monthly data.
Working with XY and Bubble Charts
Bar charts, column charts, combination charts, line charts, and a few other variants are
charts that are continuous on one axis and discrete on the other. This is fine for histograms
and the like, but it offers little benefit when you need both the horizontal and vertical axes
to be continuous. Xcelsius 2008 provides two kinds of continuous charts: XY and bubble
charts.
Each data series in an XY chart houses data for a range of values along the X-axis and a cor-
responding range of values along the Y-axis. This affords a lot of interesting possibilities.
5
Working with XY Charts
Suppose you have some raw data on individuals’ years of education and age (see Figure 5.18).
Figure 5.18
XY charts displaying
representative demo-
graphic data.
142
Chapter 5 Using Charts and Graphs to Represent Data
The tabular data to the right of the chart is just a small segment of the full dataset. An XY
chart gives you the ability to specify a number of features of your data series, including the
series shape, fill color, marker size, and transparency (see Figure 5.19).
Figure 5.19
Customizing the data
series appearance in
an XY chart.
N O T E
If you are displaying more than one data series, you cannot individually set the marker
size and transparency for each series.
5
XY charts can display only two sets of values at any time—one on the X-axis and the other
on the Y-axis. However, you might have a multitude of factors from which to select. It
would be great to start from a list of parameters—such as age, income, and education—and
choose which two go onto the XY chart. There are three ways to do this:
■ Using a List Builder component
■ Using naive lists
■ Using intelligent lists
At first glance, using List Builder would appear to be the natural way to do this. You may
have 10 or 20 kinds of parameters that you want to make available for plotting on an XY
chart. With List Builder, it is easy to choose more parameters than an XY chart can accom-
modate (see Figure 5.20). In such a case, the extra parameters are ignored. One thing you
don’t want to do is to surprise a dashboard user by inadvertently withholding information
he or she expects to see.
Working with XY and Bubble Charts
143
Figure 5.20
List Builder doesn’t
stop you if you select
more than two
parameters.
There are a couple other reasons to avoid using List Builder to create an XY chart. List
Builder copies data to a location. If the original data changes, the changes are not reflected
in the chart until the List Builder is updated. In addition, any time the user wants to switch
which items appear in a chart, he or she must go back to the List Builder chart and recon-
struct the list. List Builder may be indispensable for constructing reports, but it does not
always provide the fluid interactivity needed for dashboards.
Another approach would be to supply for each axis a list-like selector such as a List Box or
Radio Button component and, based on the parameter selected, look up the respective
dataset. This technique overcomes the primary challenges of using List Builder: It is not
possible to oversaturate the XY chart with too many parameters, and there is no wait time;
as soon as an item is selected from the list, the data appears on the plot. There is one
wrinkle with using a List Box or Radio Button component, though: It is possible to select
the same item in each of the independent lists. For instance, it is possible to plot income on
both the X-axis and Y-axis. Although this is not problematic, it isn’t very elegant.
You can use a strategy that automatically eliminates the item chosen from the list (see
5
Figure 5.21). This strategy involves what I call correlated lists. There is a list for the X-axis
and one for the Y-axis. Notice in Figure 5.21 that the X-axis list box has three items, and the
Y-axis list box has two items. Also notice that the item selected in the X-axis box is conspicu-
ously absent from the Y-axis box. This is by design. No matter which item is chosen in the
X-axis box, it is automatically eliminated from the Y-axis box.
Figure 5.21
You can select param-
eters in the XY chart
by using correlated
list boxes.
144
Chapter 5 Using Charts and Graphs to Represent Data
Let’s look at some implementation details. Your List Box component should be based on
inserting values, not position (see Figure 5.22).
Figure 5.22
List box properties
for the X-axis.
In cells C3, C4, and C5, you need to place the value 1, 2, and 3 (see Figure 5.23). In cells
C6, C7, and C8, you need to place the labels Yrs education, Age, and Income.
Figure 5.23
Spreadsheet setup for
correlated list boxes.
5
Your list boxes for the X-axis and Y-axis should be reading the labels from your underlying
spreadsheet (cells C6:C8 and D6:D7).
The formulas for cells D3 through D7 get a little complicated. I leave you to explore these
on your own in the file ch05_XYChart.xlf. Essentially, the logic behind them is that if an
item was already selected for the X-axis omit this item for the Y-axis and go to the next item
in the list.
N O T E
Depending on the quantity of data, XY charts and bubble charts can consume a fair
amount of time and CPU resources in opening the XLF file and adding data to the
components.
To complete the picture, the datasets that are chosen (the shaded cells on the right side of
Figure 5.24) are retrieved for display in the XY chart (the left side of Figure 5.24).
Working with XY and Bubble Charts
145
Figure 5.24
Datasets are chosen
for graphical display.
In this example, you can choose any 2 of 3 data sets. There is nothing to stop you from set-
ting up your dashboard to select from, say, 20 possible datasets. Unlike using the List
Builder approach, with this method, the retrieved data is still live. Changes to source data
for the values plotted are instantly reflected in the chart.
Extending Graphical Presentation with Bubble Charts
The bubble chart can be regarded as the sibling of the XY chart. The essential differences
between the two are that in a bubble chart, the marker size is variable, based on the value of
5
some data, and the marker shape is round. Bubble charts offer a convenient way to pack
more information into a chart. Rather than being forced to choose two of three parameters,
you can simultaneously display all three in a single chart. With a bubble chart, you need to
decide which parameter is associated with the X-axis, which parameter is associated with the
Y-axis, and the bubble size (see Figure 5.25).
Figure 5.25
You can choose bub-
ble chart parameters
from the list boxes.
146
Chapter 5 Using Charts and Graphs to Represent Data
Bubble charts use size to represent a quantity. So how would you represent a negative quan-
tity with size? Does a circle implode in on itself and invert its color? Xcelsius 2008 does not
provide a particularly elegant solution for negative values. Basically, it shrugs its shoulders
and gives you a little dot that is non-changing in size.
Fortunately, there’s a workaround that allows for a relatively clean implementation. The
setup is quite simple. You position your data to display your X coordinates, Y coordinates,
and bubble size (see columns B, C, and D in Figure 5.26).
Figure 5.26
Setting up a bubble
chart to support
negative values.
5
The next step is to separate positive and negative sizes (columns E and F of Figure 5.26).
The respective formulas in columns E and F could be something like this:
=IF(D2>=0,D2,””) positive values in column E
=IF(D2<0,D2,””) negative values in column F
Next, create a data series for the positive and negative values (see Figure 5.27). (You’ll learn
the details behind the halo sensors shortly.)
In the Appearance tab, set the color of the positive data series to something like green and
set negative values to red. Choose whatever colors suit your needs.
In this particular example, all the data is static except for a single data point, whose size can
be adjusted with a slider to both positive and negative values. The X and Y coordinates for
this data point use formulas that incorporate the size. Consequently, the data point moves as
you adjust the slider, and the chart automatically rescales. This example is a little contrived,
but it helps to make the essential concepts and their implementation clear.
Working with XY and Bubble Charts
147
Figure 5.27
Properties of the data
series detailing nega-
tive values.
Displaying Values of Individual Data Points
At this time, Xcelsius 2008 does not natively support the display of negative sizes. Negative values for bubble
size are rendered as tiny dots that never change size. In the preceding section, you used a little trick to fool
Xcelsius into treating negative size bubbles as if they are positive and at the same time, change their colors. This
size/color combination renders correctly, but Xcelsius still thinks the bubble size is a positive value. When your
mouse hovers over the “negative” size bubble, the hover text displays the correct X and Y coordinates, but the
negative value for size shows up as a positive number.
5
Rather than deliver a broken dashboard, it is better to turn off the mouse over text. But don’t despair. I know
another useful trick. By using the drill down capability of the bubble chart, it is easy to extract which point the
mouse is positioned over and push relevant data about the point, including the negative bubble size, to a table
that’s suitable for displaying the data. I call this technique the halo sensor. With this method, you surround
each data point with a thin ring, or “halo,” that is capable of sensing when the mouse is positioned over it. It
uses the drill down feature of the chart to identify which data point has focus. When the data point is identified,
its related information can be easily retrieved.
The halo is set up as a separate data series. It visually appears behind the positive and “negative” size circles. If
a halo were smaller than or the same size as these positive or negative circles, it would be eclipsed.
Consequently, the halo needs to be a larger size than the circle size for the data points. Doubling the size
seems to work well.
When you enable drill down, you may want to set the interaction options to mouse over instead of mouse click.
Incidentally, you can drill down with each of the series as long the insertion points do not overlap.
148
Chapter 5 Using Charts and Graphs to Represent Data
Working with Tree Maps
New to Xcelsius 2008 is the Tree Map component. Tree Map components simultaneously
use color and size to represent data pairs, such as median income level and employee
turnover. A Tree Map component is a collection of non-overlapping colored tiles that com-
pletely fill up a large rectangle (see Figure 5.28).
Figure 5.28
Tree Map compo-
nents display data by
size and color and
support drill down.
Each tile represents a row of data. Its size corresponds to the relative contribution of a spe-
cific measure, such as sales volume. The color of each rectangle can represent a different
kind of measure, such as profitability.
5
A Tree Map component automatically arranges the tiles based on size and then by color
or shading.
Tree maps are pretty, but unless you can easily connect them to data they use, their benefits
are limited. In a world where there are lots of different kinds of data to examine, it would be
nice to be able to choose datasets as easily as you can with the XY chart examples outlined a
few pages ago.
Figure 5.29 shows the spreadsheet used to create the dashboard shown in Figure 5.28. The
dataset in column C determines the tile size on the tree map. The dataset in column C is
used to set the shading of colors for each of the tiles. When you start thinking about placing
your data in two columns, one of which shows up as tile size and the other as tile color, the
setup of a tree map becomes particularly easy to envision. The greater complexity comes
about by shuttling data so that it is conveniently easy for a tree map to use.
Rather than reinvent the wheel, it makes sense to reuse spreadsheet designs already devel-
oped and vetted. You can use one of the spreadsheets already prepared in this chapter (refer
to Figure 5.23) for the tree map.
Working with Tree Maps
149
Figure 5.29
The spreadsheet
setup for a tree map.
In fact, the spreadsheet of Figure 5.25 was actually used to build this dashboard. Basically,
the data was swapped, and a few formulas were tweaked. There is also a little extra work
involved in drilling down to detailed information based on the selected tile.
Before we leave the topic of tree maps, I need to mention a few things about them:
■ The hover text in a tree map typically consumes a fair amount of screen space. It can
easily obscure other relevant data. For this reason, the drill down data is placed below
the tree map and not to the right of it.
5
■ When selecting colors for high and low values, try to stay in the same color family and
vary the brightness.
■ Each data series in a tree map consists of a pair of correlated datasets—one column for
the size and the other for color. If you want to add a second series, place the data imme-
diately to the right of the first data series.
■ The tile area, and not the tile length or width, is proportional to its underlying data. If
sales increased by a factor of 9, the relative length and width of the tile would increase
by a factor of 3. This is both a good and bad thing. Because the total area for the whole
tree map remains conserved, the other tile sizes get scaled down by a lesser amount.
Small values don’t get diminished so quickly. It is also more difficult to interpret
because we are used to linear proportionality, but in a tree map, tile size is proportional
to the square root of its underlying data.
While a tree map may be pretty to look at, it doesn’t do anything that an XY chart doesn’t.
Actually, an XY chart can be easier to interpret than a tree map. If you stop and think about
it, the data points in an XY chart are, by definition, already sorted.
150
Chapter 5 Using Charts and Graphs to Represent Data
Issues and Techniques Related to Scaling
Xcelsius 2008 provides for auto-scaling of charts. This relieves you of the burden and drudg-
ery of manually setting a chart scale. Most of the time, auto-scaling works well, but if your
living is based on presentations and dashboards, you might want more fine-tuned control
than auto-scaling allows.
Consider the following data regarding estimates of manufacturing efficiency:
day production efficiency
7 59%
14 88%
21 91%
28 99%
Depending on real-world circumstances, the data scale that auto-scaling chooses may or may
not be appropriate (see Figure 5.30). In this example, the scale reaches 120%. In terms of
manufacturing efficiency, 120% is a physically meaningless quantity. Except for reporting or
rounding errors and incorrectly calculated estimates, manufacturing efficiency would not
exceed 100%.
Figure 5.30
Auto-scaling can go
well beyond the data
extremes.
5
The point here is that there will be times you will want to take charge of how Xcelsius
scales the data in your charts. With the aid of spreadsheet formulas you can design, you may
be able to create the scaling behavior you are looking for.
Exploring the Scaling Laboratory
Rather than try to explain the intricacies of the various permutations and combinations of
scaling settings, in this section I provide you with a scaling laboratory dashboard (see
Figure 5.31 or have a go at it with ch05_ScalingLab.xlf).
In the scaling lab dashboard, you have the option of specifying how minimum and maxi-
mum scales are handled.
Issues and Techniques Related to Scaling
151
Figure 5.31
Chart scaling dash-
board for which you
can adjust the data
extremes.
This dashboard has two data series, which are displayed in a combination chart. The data
used for the chart is displayed in a table (on the right side of Figure 5.31). Notice that two
of the data points in this table are shaded. You can adjust the values for the two data points
by using the vertical sliders immediately above the data table. The vertical sliders allow you
to dynamically adjust values plotted on the chart, so you can see what happens based on the
prevailing scaling behavior.
You set the scaling behavior by clicking the various options in the two list boxes near the
upper-left portion of the dashboard.
5
Here is a brief description of the various terms in the Minimum list box:
■ Use Minimum Value: This is the minimum value of all the data points displayed in the
data table. It includes the values from both series.
■ minValue - x%: This is the minimum value reduced by an extension factor. You can
adjust this extension factor by using the horizontal slider labeled Extension Factor near
the top-right side of the dashboard.
■ Min - x% of Delta: This takes the minimum value of all data points and sets the lower
limit of the scale to be a set percentage of the difference between the maximum and
minimum values of both data series. If all your data is concentrated over a narrow range
of values, this type of scaling would be appropriate.
■ Fixed Min of x: This hardwires the lower limit of the scale to a fixed number. You have
the option of setting this value by using a slider. Once you set it, the value is unchang-
ing until you decide to manually revise it.
■ Zero based: This option hardwires the scale’s lower limit to 0.
152
Chapter 5 Using Charts and Graphs to Represent Data
The Maximum list box options are largely the equivalent of those in the Minimum list box,
except that they apply to the scale’s upper limit and tend to add rather than subtract values.
In addition, there is no zero-based equivalent for the Maximum list box.
N O T E
Keep in mind that if you don’t like the way Xcelsius is handling scaling—for example, if it
is creating scaling limits clearly beyond 100%—you need to be able to handle both the
upper and lower limits of the scale. You can’t get away with addressing only one side of
the spectrum.
Dealing with Vastly Different Values on the Same Chart
Sometimes you can get caught with having quantities such as 10, 100, and 60,000 in the
same chart. If you place these on a linear plot, the small values will virtually disappear. If
you are tabulating information such as loss or impairment of an asset and frequency of
occurrence, then you definitely don’t want to forgo treating the infrequent but very expen-
sive events in your data analysis.
Figure 5.32 shows government-published data on number of oil pipeline accidents versus
barrels lost in the United States during 2006. There is a remarkable level of linearity on the
upper limit for the number of accidents.
Figure 5.32
A LogLog scale (that
is, logarithmic scaling
on both the X- and
Y-axes) reveals struc-
tured relationship
5
over many orders of
magnitude.
Setting up logarithmic scaling is rather straightforward. You simply open the Scale subtab of
the chart’s Behavior tab and select Logarithmic for both Horizontal and Vertical Axis Scale
(see Figure 5.33). You can also experiment with applying logarithmic scaling for only one of
the axes.
Issues and Techniques Related to Scaling
153
Figure 5.33
Specifying logarithmic
scaling on an XY
chart.
What happens if you keep both axes linear? The details for the smaller values are almost
completely lost because they are too small to be seen (see Figure 5.34).
Figure 5.34
The smaller values for
barrels lost are too
small to discern in
5
linear scaling.
154
Chapter 5 Using Charts and Graphs to Represent Data
Putting Visual Data Analysis into Focus
Before leaving this chapter on charting, I want to address a key point that has been nagging
at me ever since I started doing work with visual data analysis and dashboards. Many people
who work on dashboard design are literally consumed with cramming as much as they can
onto a single screen. They revel in the aesthetics of interface design and are quick to criti-
cize a dashboard layout without offering alternatives or workarounds.
Understandably, it is very easy to get consumed with the visual interface and its aesthetics.
One of the major premises of the dashboard is that an executive or a decision maker who
uses it may have a limited opportunity to closely examine details, so the dashboard has to
bring together all the information in one place. The information has to be easy to consume.
Focusing on the interface design without taking into account the mechanics, tasks, and prac-
tical challenges of building the visual interface is like coming up with a requirements docu-
ment: It is a starting point but not a solution.
One of the alluring features of Xcelsius is that you can use it to harness a spreadsheet engine
to intelligently and dynamically feed the dashboard visual display with appropriate informa-
tion when and where it is needed. When information moves in such a fluid fashion, you can
begin relaxing the all-consuming need to cram data onto a single screen. This, in turn, helps
you sensibly design your dashboards and visualizations with greater simplicity and clarity.
Closing Thoughts
The goal of visual data analysis is to make obvious implicit and otherwise difficult-to-discern
relationships. I started out this chapter by saying that every dashboard should have a story to
tell. Sometimes, it’s the dashboard designer who knows exactly what has to be said and is in
5
need of a way to masterfully present the message. Other times, the dashboard lets the data
speak for itself, by making it easy for the end user to examine and explore the data with ease
and turn over stones that would otherwise be left untouched. I refer to the latter as “planned
serendipity.”
This chapter presents key issues, possible strategies, useful techniques, and hidden gotchas
that tend to come up when presenting data visually. Along the way, many of the principles
and techniques are shown in action.
The best place to begin is to ask, which components do I use? Then you can tackle, how do
I tame the data? One answer is to put the data on a timeline. In this manner, you can see
trends but not be overwhelmed by a dizzying array of distracting information competing for
your attention.
In some cases a dashboard may be otherwise well designed, but the cosmetics get in the way.
Xcelsius automatically enables data animation, which gives the dashboard a certain coolness
and is designed to “wow” the audience. Unfortunately, when you are trying to analyze pat-
terns and trends, the jittery behavior of this feature can be downright distracting!
Closing Thoughts
155
As you get more sophisticated in your dashboard skills, you are bound to combine several
components so they work as one. People often forget to make components visually blend
together as if they are one larger component. Sometimes all it takes to glue them together is
a single visual background. Sometimes it makes sense to stack data together so you can see
all the data together at one time. Stacked data and its components become further empow-
ered when you can drill down to get at the underlying details.
A common perceived limitation of pie charts is that they are only well suited to situations in
which the various slices of the pie are roughly similar in size, and there are not too many of
them. However, by dynamically grouping the smallest slices, you can use pie charts in many
other situations as well.
Many of the Xcelsius charting components, such as column charts, are designed to handle
histogram-like data where one of the axes is continuous, and the other varies in discrete
measures or categories. There are times when it is necessary to get more quantitative and
display two or more measures. This is where XY charts, bubble charts, and tree maps come
into play. Standing behind these charts can be a variety of different kinds of datasets, waiting
to be visually mixed and matched. I introduce a technique of using correlated list boxes to
seamlessly select the datasets to be displayed. This technique takes context switching to an
extreme.
In this chapter, you saw a solution for rendering bubble charts when the bubble sizes have
negative values. You also learned how to set up tree maps. In addition, you learned about
chart scaling because it is important to be able to fully control a dashboard’s visual elements.
The theme of visual elements continues into Chapter 6, “Single Value Components: Dials,
Gauges, Speedometers, and the Like.”
5
index
Symbols
- (negation operator), 81
generally accepted
<> (not-equal operator), 414
accounting principles
+ (addition operator), 414
(GAAP), 261
: (range operator), 80, 414
<> (angle brackets), 303
income statements, 262
∑ (sigma), 235
matching, 262
= (assignment operator), 412
- (subtraction operator), 414
statement of cash, 262
’ (comment operator), 413
, (union operator), 80
accrual-basis accounting, 262
& (concatenation
ACOS function, 97-98, 405
operator), 414
A
ACOSH function, 405
/ (division operator), 412
activating visibility patterns
$ (dollar symbol), 74
abbreviations for chart
with switch circuit, 197
= (equal symbol), 71
labels, 206-207
Actual Budget Comparison
^ (exponentiation
ABC (Actual Budget
(ABC) charts, 344-346
operator), 413
Comparison) charts,
344-346
Add-On Packager, 387-388,
> (greater-than
390-391
operator), 413
ABS function, 96-97, 405
adding in quadrature
>= (greater-than-or-equal-to
absolute numbers, 96
(uncertainty analysis), 39
operator), 413
absolute references, 73-74
addition operator (+), 414
< (less-than operator), 413
Accordion Menu
aggregation functions, 82-84
<= (less-than-or-equal-to
component, 209
AVERAGE, 83
operator), 413
Accordion view, 20-21, 47
AVERAGEA, 83
= (logical equality
accounting. See also
COUNT, 84
operator), 412
financial analysis
COUNTA, 84
- (minus operator), 413
accrual-basis
COUNTIF, 84-85
m
accounting, 262
(mu), 243
LARGE, 89
balance sheets, 262
MAX, 82
* (multiplication
cash-basis accounting, 262
MEDIAN, 83
operator), 414
costs, 262
MIN, 82
432
aggregation functions
MODE, 83
costs, 262
attributes, binding to
RANK, 89
financial ratio analysis,
underlying spreadsheet,
SMALL, 89
266-272
24-26
SUM, 85
generally accepted
Augmented Map Framework,
SUMIF, 87-88
accounting principles
280-283
SUMPRODUCT, 86-87
(GAAP), 261
Auto option (chart
SUMSQ, 88
income statements, 262
scaling), 206
aging reports, 264-266
matching, 262
auto-scaling, 150
overview, 258
alerts. See also smart text
real-world judgments
AVEDEV function, 408
advantages of, 312
and, 272-273
AVERAGE function, 83,
built-in alerts, 298
statement of cash, 262
90, 408
colorized alerts, 34-35
time, accounting for,
colorizing, 301-302
AVERAGEA function,
263-264
components with built-in
83, 408
Value at Risk (VaR),
alerts, 298
averages, calculating, 83, 235
258-261
components without built-in
statistical analysis.
avoiding
alerts, 299
See statistics
data series congestion,
enabling, 300
137-138
with filled radar charts,
AND function, 107, 404
occlusion, 342-343
341-342
angle brackets, 303
inline alerts, 309-310
animation, enabling/
multiple data series and, 301
B
disabling, 206
overview, 298
apostrophe (‘), 413
Background component,
value-based alerts, 300
Appearance tab (Properties
61-62, 220-222
Alerts tab (Properties
panel), 24
background groups, 194
panel), 24
art components, 61-62,
balance sheets, 262
Altman, Ed, 271
418-419, 422
BasicHorizontalSlider
ampersand (&), 414
ASIN function, 97, 405
connecting ActionScript and
analysis
ASINH function, 405
MXML code, 385
financial analysis
creating ActionScript code
assignment operator (=), 412
accrual-basis
for, 378-384
accounting, 262
asterisk (*), 414
generating SWF files for,
aging reports, 264-266
ATAN function, 97, 405
385-386
balance sheets, 262
ATAN2 function, 99, 405
cash-basis
ATANH function, 405
accounting, 262
CHOOSE function
433
Bayes’ Theorem, 241-242
caret (^), 413
charts
Behavior tab (Properties
cash, statement of, 262
ABC (Actual Budget
panel), 24
Comparison) charts,
cash-basis accounting, 262
344-346
BETADIST function, 408
Category I digitization
avoiding occlusion, 342-343
binding attributes to
errors, 317
bubble charts, 145-146
underlying spreadsheet,
Category II digitization
Candlestick components,
24-26
errors, 317
347-350
binomial distributions,
Category III digitization
charting multiple data
244-246
errors, 318
series with similar values,
branding visual
CDF (cumulative
339-341
elements, 194
distributions function), 246
choosing for dashboards,
bubble charts, 145-146
128-131
CEILING function, 100, 405
column charts, 56-57,
budgets, ABC (Actual
cells
129-131
Budget Comparison)
evaluating, 79-80
combination charts, 132-134
charts, 344-346
formatting in Spreadsheet
dynamic data grouping,
built-in alerts, 298-299
Table component, 224-225
137-138
buttons, toggle buttons, 190
named ranges, 77-79
filled radar charts, 341-342
references, 73
interface design, 154
absolute references,
C
label abbreviations, 206-207
73-74
line charts, 343-344
in complex tables, 327
calendar arithmetic
pie charts, 54-56, 139-140
example, 75-76
at dashboard level, 114-115
scaling, 204-206
hybrid cell references,
setting up on spreadsheets,
auto-scaling, 150
74-75
113-114
logarithmic scaling,
isolating hardwired
152-153
Calendar component, 223
values, 76-77
scaling laboratory
Candlestick components,
in multiplication
dashboard, 150-152
347-350
tables, 327
stacked charts, 135-136
canvas
percentage symbols
tree maps, 148-149
adding components to,
in, 76
Xcelsius product family
19-21
relative references, 74
comparison, 419
definition of, 46
in VLOOKUP formula,
XY charts, 141-145
resizing, 48
326-327
Check Box component, 189,
capabilities of Xcelsius,
chaining formulas, 72
209-210
16-17
CHOOSE function, 123, 407
How can we make this index more useful? Email us at indexes@samspublishing.com
434
client portals
client portals, 37
attributes, binding to
pasting between XLF
colon (:), 80, 414
underlying spreadsheet,
files, 63
24-26
visibility
color
choosing for dashboards,
dynamic visibility,
adding to alerts, 301-302
128-131
188-189
adding to maps, 283-286
connection
multi-layer visibility,
specifying in HTML, 220
components, 395
192-200
colorized alerts, 34-35
context switching, 62-63
mutually exclusive
Column Chart component,
custom components
visibility, 190-191
56-57, 129-131
connecting ActionScript
overview, 188
COMBIN function, 408
and MXML code, 385
toggling, 189-190
Combination Chart
construction
Web connectivity, 62, 422
component, 132-134
workflow, 373
Components command
creating ActionScript
Combo Box component,
(View menu), 19
code for, 378-385
traffic light alerts in,
Components pane, 19
enhancing, 393-394
210-211
definition of, 46
Flex Builder
comma (,), 80
views, 20, 47
environment, 373-375
comment operator (‘), 413
CONCATENATE
generating SWF files for,
function, 119, 411
Compatibility Mode, 52
385-386
installing, 372-373
concatenation operator
complex tables, cell
loading, 391-393
(&), 414
references in, 327
overview, 372
conditional formulas,
complexity
packaging, 387-391
107-108
containment through tab
potential problems,
sets, 182-184
conditional operators, 106
395-396
managing with context
conditional probability,
relationship with
switching, 184-188
239-242
property sheets,
Component pane,
386-387
configuring Flex Builder, 377
positioning, 51
software requirements,
connected map
component visibility,
375-376
dashboards, 35
managing, 38
testing, 391-393
Connected Maps reference
components. See also
function components, 395
implementation, 291-294
specific components
grouping, 50
connection components, 395
adding to canvas, 19-21
hidden components, 50
connectivity. See
adding to dashboards,
invisible components, 50
data connectivity
128-131
list of new components, 40
dashboards
435
constructing dates, 110-111
construction workflow, 373
D
containers
creating ActionScript code
panel containers, 58
for, 378-385
dashboards. See also
tab sets, 58
enhancing, 393-394
specific components
Xcelsius product family
Fisheye component,
building, 44-45, 52
comparison, 420
211-212
colorized alerts, 34-35
Flex Builder environment,
complexity
context switcher
373-375
containment through tab
(Shared Component
generating SWF files for,
sets, 182-184
Framework), 170
385-386
managing with context
context switching, 2, 41,
installing, 372-373
switching, 184-188
62-63, 184-188
loading, 391-393
component visibility,
continuous probability
overview, 372
managing, 38
distributions, 246-250
packaging, 387-391
connecting to Web Services,
converting text to numbered
potential problems, 395-396
365-368
equivalent, 105
relationship with property
customizing appearance of,
27-29
correlated lists, 143
sheets, 386-387
Sliding Picture Menu
desktop client portals, 37
COS function, 97-98, 405
component, 211-212
embedded formulas in, 34
COSH function, 405
software requirements,
exporting, 26
costs, 262
375-376
financial analysis in, 38-39
COUNT function, 84, 409
testing, 391-393
graphical viewers in, 36
Ticker component, 211
limits, 29-30
COUNTA function, 84, 409
customizing
making date and time aware
COUNTIF function,
components. See
calendar arithmetic,
84-85, 409
custom components
113-115
counting functions, 84-85
dashboard appearance,
DATE function, 110
cross-domain policy files,
27-29
DATEVALUE
368-369
workspace layout, 47
function, 110
cumulative distributions
canvas size, 48
DAY function, 111
function (CDF), 246
Components Pane
DAYS360 function, 112
EDATE function, 112
custom components
views, 47
EOMONTH
Check Box component,
Object Browser, 48-50
function, 112
209-210
pane layout, 51
HOUR function, 111
connecting ActionScript and
MINUTE function, 111
MXML code, 385
MONTH function, 111
How can we make this index more useful? Email us at indexes@samspublishing.com
436
dashboards
NETWORKDAYS
templates, 63-64
data updates, Xcelsius
function, 111-112
themes, 64-65
product family
NOW function, 110
data animation, enabling/
comparison, 417
overview, 110
disabling, 206
data visualization
SECOND function, 111
data bank (Shared
avoiding occlusion, 342-343
TIME function, 110
Component
filled radar charts with
TIMEVALUE
Framework), 170
alerts, 341-342
function, 111
line chart data, 343-344
data connectivity
TODAY function, 110
multiple data series with
cross-domain policy files,
WEEKDAY
similar values, 339-341
368-369
function, 113
overview, 339
open-ended dashboards, 355
WEEKNUM
packaging dashboard
date and time awareness,
function, 113
information, 354
adding to dashboards
YEAR function, 111
Web Services, 365-368
calendar arithmetic
YEARFRAC
Xcelsius Data Manager, 355
at dashboard level,
function, 113
XML
114-115
multiple information
Excel XML maps,
setting up on
sources in single view, 37
359-364
spreadsheets, 113-114
open-ended dashboards, 355
overview, 356
DATE function, 110
open-source dashboards,
setting up for Xcelsius,
DATEVALUE
spreadsheets as, 13-16
357-359
function, 110
packaging, 354
DAY function, 111
ratio analysis in, 39
data digitization
DAYS360 function, 112
scaling laboratory
Category I digitization
EDATE function, 112
dashboard, 150-152
errors, 317
EOMONTH function, 112
sensitivity analysis in, 38
Category II digitization
HOUR function, 111
Shared Component
errors, 317
MINUTE function, 111
Framework, 36
Category III digitization
MONTH function, 111
statistical analysis in, 38
errors, 318
NETWORKDAYS
timeline viewers in, 37
overview, 316-317
function, 111-112
uncertainty analysis in, 39
Data Insertion feature
NOW function, 110
uniform look and feel,
(maps), 278
overview, 110
designing
Data Manager, 355
SECOND function, 111
context switching, 62-63
data series
TIME function, 110
fonts, 66-67
congestion, avoiding,
TIMEVALUE
pasting components
137-138
function, 111
between XLF files, 63
drilling down, 207-208
TODAY function, 110
multiple data series, 301
WEEKDAY function, 113
enabling
437
WEEKNUM function, 113
dialog boxes. See specific
DMAX function, 409
YEAR function, 111
dialog boxes
DMIN function, 409
YEARFRAC function, 113
dials
dollar ($) symbol, 74
DATE function, 110, 402
enhancing, 168-169
DOLLAR function, 101, 406
date/time functions, 402-403
overview, 166-167
DPRODUCT function, 406
scaling, 167-168
DATEVALUE function,
Shared Component
drilling down
110, 402
Framework, 169-171
in data series, 207-208
DAVERAGE function, 402
sharing, 169
with pie charts, 139-140
DAY function, 111, 402
digitization of data
DSTDEV function, 409
DAYS360 function, 112, 402
Category I digitization
DSTDEVP function, 409
DB function, 92, 403
errors, 317
DSUM function, 409
DCOUNT function, 409
Category II digitization
dual sliders, 164-166
errors, 317
DCOUNTA function, 409
Category III digitization
DVAR function, 409
DDB function, 92, 403
errors, 318
DVARP function, 409
decimal numbers, 219
overview, 316-317
dynamic data grouping,
defining named ranges, 78
disabling data animation, 206
137-138
DEGREES function,
discrete probability
dynamic visibility, 50,
97-99, 405
distributions, 242-246
188-189
depreciation methods, 93-94
display data (in maps),
design
277-278
E
uniform look and feel
displaying negative
context switching, 62-63
values, 147
EDATE function, 112, 402
fonts, 66-67
distribution (probability)
efficiency features,
pasting components
binomial distributions,
Xcelsius product family
between XLF files, 63
244-246
comparison, 417
templates, 63-64
continuous probability
embedded alerts, 309-310
themes, 64-65
distributions, 246
embedded formulas, 34
visual data analysis, 154
discrete probability
embedded spreadsheets.
desktop client portals, 37
distributions, 242-244
See spreadsheets
Details tab (Add-On
normal distribution,
enabling
Packager), 388
247-250
alerts, 300
uniform distribution, 92
DEVSQ function, 409
data animation, 206
division operator (/), 412
DGET function, 407
How can we make this index more useful? Email us at indexes@samspublishing.com
438
enhancing
enhancing
EXPONDIST function, 409
financial ratio analysis,
custom components,
exponentiation operator
266-267
393-394
(^), 413
ratio analyzer dashboard,
dials, 168-169
267-270
exponents, calculating,
EOMONTH function,
Z score, 271-272
102-103
112, 402
generally accepted
exporting data
accounting principles
equal sign (=), 71, 412
dashboards, 26
(GAAP), 261
errors. See troubleshooting
Xcelsius product family
income statements, 262
Escape from Excel Hell
comparison, 418
matching, 262
(Abdulezer), 71, 310
Extensible Markup
overview, 258
ETC (Evolving Technologies
Language. See XML
real-world judgments and,
Corporation) Shared
272-273
Component Framework,
F
statement of cash, 262
169-171
time, accounting for,
evaluating spreadsheet
FACT function, 409
263-264
cells, 79-80
FALSE function, 106
Value at Risk (VaR),
258-261
EVEN function, 100, 406
FALSE value, 412
financial functions, 92,
Evolving Technologies
files
403-404
Corporation (ETC) Shared
cross-domain policy files,
depreciation methods, 93-94
Component Framework,
368-369
net present value, 94-95
169-171
MapRegions.xls, 290
FIND function, 118, 411
EXACT function, 119, 411
SWF files, generating
for custom components,
FISHER function, 409
Excel
385-386
installing, 18
FISHERINV function, 409
XLF files, pasting
spreadsheets.
Fisheye component,
components between, 63
See spreadsheets
customizing, 211-212
trialware package, 18
filled radar charts, 341-342
FIXED function, 406
XML
Filter component, 214-216
Flash Player, 17
setting up for Xcelsius,
financial analysis, 38-39
Flex Builder, 373-377
357-359
accrual-basis
XML maps, 359-364
FLOOR function, 100, 406
accounting, 262
Excel Best Practices for
aging reports, 264-266
Focus Chart Data option
Business (Abdulezer), 310
balance sheets, 262
(chart scaling), 206
EXP function, 103, 406
cash-basis accounting, 262
Folder view, 47
costs, 262
functions
439
font support
ASIN, 97, 405
DSUM, 409
overview, 66-67
ASINH, 405
DVAR, 409
Xcelsius product family
ATAN, 97, 405
DVARP, 409
comparison, 417
ATAN2, 99, 405
EDATE, 112, 402
FORECAST function,
ATANH, 405
EOMONTH, 112, 402
104-105, 252, 406
AVEDEV, 408
evaluating spreadsheet cells
AVERAGE, 83, 90, 408
with, 79-80
forecasting trends, 104-105
AVERAGEA, 83, 408
EVEN, 100, 406
formatting
BETADIST, 408
EXACT, 119, 411
cell formatting in
CEILING, 100, 405
EXP, 103, 406
Spreadsheet Table
CHOOSE, 123, 407
EXPONDIST, 409
component, 224-225
COMBIN, 408
FACT, 409
HTML formatting
CONCATENATE,
FALSE, 106
in Input Text Area
119, 411
FIND, 118, 411
component, 218-220
COS, 97-98, 405
FISHER, 409
in Label component,
COSH, 405
FISHERINV, 409
218-220
COUNT, 84, 409
FIXED, 406
text formatting
COUNTA, 84, 409
FLOOR, 100, 406
LOWER function, 116
COUNTIF, 84-85, 409
FORECAST, 104-105,
new features, 40
DATE, 110, 402
252, 406
TEXT function, 116-117
DATEVALUE, 110, 402
FV, 94, 404
UPPER function, 116
DAVERAGE, 402
GEOMEAN, 409
formulas
DAY, 111, 402
HARMEAN, 409
adding to spreadsheets,
DAYS360, 112, 402
HLOOKUP, 124-125, 407
29-30
DB, 92, 403
HOUR, 111
chaining, 72
DCOUNT, 409
IF, 107, 404
conditional formulas,
DCOUNTA, 409
INDEX, 122-123, 270, 407
107-108
DDB, 92, 403
INT, 100, 406
embedded formulas, 34
DEGREES, 97-99, 405
INTERCEPT, 105,
simple formulas, 71-72
DEVSQ, 409
252, 406
structure of, 71
DGET, 407
IPMT, 94, 404
troubleshooting, 325-327
DMAX, 409
IRR, 94, 404
functions, 395
DMIN, 409
ISBLANK, 107, 404
ABS, 96-97, 405
DOLLAR, 101, 406
ISERR, 108, 404
ACOS, 97-98, 405
DPRODUCT, 406
ISERROR, 108, 405
ACOSH, 405
DSTDEV, 409
ISEVEN, 109, 405
AND, 107, 404
DSTDEVP, 409
ISLOGICAL, 109, 405
How can we make this index more useful? Email us at indexes@samspublishing.com
440
functions
ISNA, 108, 405
operators, 80-81
SUMXMY2, 411
ISNONTEXT, 109, 405
OR, 107, 405
SYD, 92, 404
ISNUMBER, 108, 405
overview, 79
TAN, 97-98, 407
ISODD, 109, 405
PI, 97-98, 406
TANH, 407
ISTEXT, 109, 405
PMT, 94, 404
TEXT, 116-117, 411
KURT, 410
POWER, 102, 406
TIME, 110, 403
LARGE, 89, 410
PPMT, 94, 404
TIMEVALUE, 111, 403
LEFT, 117, 411
PRODUCT, 105, 406
TODAY, 110, 403
LEN, 118, 411
PV, 94, 404
TRUE, 106
LN, 103, 406
QUOTIENT, 101, 406
TRUNC, 101, 407
LOG, 103, 406
RADIANS, 97-98, 406
TYPE, 109, 408
LOG10, 103, 406
RAND, 91-92, 410
UPPER, 116, 411
LOOKUP, 408
RANDBETWEEN, 92
VALUE, 105, 407
LOWER, 116, 411
RANK, 89, 410
VAR, 90-91, 411
MATCH, 123, 408
RATE, 94, 404
VDB, 92, 404
MAX, 82, 410
REPLACE, 118
VLOOKUP, 124-125, 305,
MEDIAN, 83, 410
REPT, 118, 411
326-327, 408
MID, 118, 411
RIGHT, 411
WEEKDAY, 113, 403
MIN, 82, 410
ROUND, 100, 406
WEEKNUM, 113, 403
MINUTE, 111
ROUNDDOWN, 100, 407
WORKDAY, 403
MIRR, 94, 404
ROUNDUP, 100, 407
Xcelsius support for, 22
MOD, 101, 406
SECOND, 111, 403
YEAR, 111, 403
MODE, 83, 410
SIGN, 96, 407
YEARFRAC, 113, 403
MONTH, 111, 403
SIN, 97-98, 407
FV function, 94, 404
N, 105, 406
SINH, 407
NETWORKDAYS,
SLN, 92, 404
G
111-112, 403
SMALL, 89, 410
NORMDIST, 92, 410
SQRT, 80, 407
GAAP (generally accepted
NORMINV, 92, 410
STANDARDIZE, 410
accounting principles ), 261
NORMSINV, 410
STDEV, 90-91, 410
gauges
NOT, 107, 405
SUM, 85, 410
example, 171-173
NOW, 110, 403
SUMIF, 87-88, 410
overview, 171
NPER, 94, 404
SUMPRODUCT,
wraparound gauges,
NPV, 94, 404
86-87, 411
174-175
OFFSET, 2, 119-122,
SUMSQ, 88, 411
269-270, 309, 408
SUMX2MY2, 411
General tab (Properties
SUMX2PY2, 411
panel), 24
Interactive Calendar component
441
generally accepted
hidden components, 50
I
accounting principles
histograms, 231-234
(GAAP), 261
Icon component, hotspots
History component, 226
GEOMEAN function, 409
for, 213-214
history of spreadsheets,
goals of Xcelsius, 16-17
IF function, 107, 404
12-13
gradients in Rectangle
if-then-else logic, 107
HLOOKUP function,
component, 221
124-125, 407
Image component, 40, 222
graphical viewers, 36
Horizontal Line
importing spreadsheets into
greater-than operator
component, 222
Xcelsius, 30
(>), 413
horizontal lines, 62
improperly structured data,
greater-than-or-equal-to
troubleshooting, 331-333
horizontal sliders, 160-161
operator (>=), 413
income statements, 262
hotspots for Icon
Grid component, 225-226
component, 213-214
INDEX function, 122-123,
grouped data
270, 407
HOUR function, 111
background groups, 194
individual data points, dis-
HTML (Hypertext Markup
components, 50
playing values of, 147
Language)
dynamic data grouping,
formatting
infix operators, 81
137-138
in Input Text Area
inline alerts, 309-310
multi-layer invisibility,
component, 218-220
Input Text Area
198-199
in Label component,
component, 218-220
viewing with stacked charts,
218-220
135-136
Input Text component,
overview, 303
216-218
Grow option (chart
rendering in Xcelsius,
scaling), 206
installing
302-305
custom components,
smart text, 304-305
372-373
H
text color, 305-306
Excel, 18
hybrid cell references, 74-75
Flex Builder, 377
halo sensor technique, 147
Hypertext Markup
Xcelsius 2008, 18
hardwired values, isolating,
Language. See HTML
Xcelsius SDK, 376
76-77
hyphen (-)
INT function, 100, 406
hardwired values,
minus operator, 413
Interactive Calendar
troubleshooting, 325-326
subtraction operator, 414
component, 40
HARMEAN function, 409
hexadecimal numbers, 219
How can we make this index more useful? Email us at indexes@samspublishing.com
442
interactivity
interactivity
Sliding Picture Menu
ISSODD function, 109
Accordion Menu
component, 211-212
ISTEXT function, 109, 405
component, 209
Spreadsheet Table
Background component,
component, 224-225
K-L
220-222
text components, 216-218
Calendar component, 223
Ticker component, 211
KURT function, 410
chart label abbreviations,
Trend Icon component, 224
206-207
Vertical Line
Label component, 53-54,
chart scaling, 204-206
component, 222
218-220
Check Box component,
INTERCEPT function, 105,
label-based menus,
209-210
252, 406
controlling multiple screens
Combo Box component,
interface design, 154
with, 192-193
210-211
international maps
labels, 53-54
data animation, 206
Connected Maps reference
drilling down in data series,
LARGE function, 89, 410
implementation, 291-294
207-208
largest values in dataset,
overview, 289
Filter component, 214-216
returning, 89
World by Continent Map
Fisheye component,
learning curves, 103
component, 290
211-212
LEFT function, 117, 411
Grid component, 225-226
invisible components, 50
LEN function, 118, 411
History component, 226
IPMT function, 94, 404
Horizontal Line
less-than operator (<), 413
IRR function, 94, 404
component, 222
less-than-or-equal-to
ISBLANK function, 107, 404
Icon component, 213-214
operator (<=), 413
Image component, 222
ISERR function, 108, 404
limits on dashboards, 29-30
Input Text Area
ISERROR function, 108, 405
line charts, viewing data in,
component, 218-220
ISEVEN function, 109, 405
343-344
Label component, 218-220
ISLOGICAL function,
lines, 62
List Box component,
109, 405
210-211
List Box component,
ISNA function, 108, 405
Local Scenario Button
210-211, 309-310
component, 223
ISNONTEXT function,
List view, 20, 47, 137
Print Button
109, 405
lists, correlated, 143
component, 222
ISNUMBER function,
LN function, 103, 406
Rectangle component,
108, 405
220-222
loading custom components,
ISODD function, 405
Reset Button
391-393
isolating hardwired
component, 223
values, 76-77
MONTH function
443
Local Scenario Button
overview, 61, 276
MOD, 101
component, 223
region keys, 277
N, 105
LOG function, 103, 406
regions, 276-277
PI, 97-98
tally maps, 288-289
POWER, 102
LOG10 function, 103, 406
World by Continent Map
PRODUCT, 105
logarithmic scaling, 152-153
component, 290
QUOTIENT, 101
logarithms, calculating,
Xcelsius product family
RADIANS, 97-98
102-103
comparison, 421
ROUND, 100
logic functions, 404-405
MapRegions.xls file, 290
ROUNDDOWN, 100
logic operators, 107
ROUNDUP, 100
maps. See Map components
SIGN, 96
logic switches, 107
MATCH function, 123, 408
SIN, 97-98
logical equality
matching, 262
TAN, 97-98
operator (=), 412
mathematical functions, 95,
TRUE, 106
logical values, 411-412
405-407. See also
TRUNC, 101
LOOKUP function, 408
aggregation functions;
VALUE, 105
LOWER function, 116, 411
financial functions;
MAX function, 82, 410
statistical functions
maximum value,
ABS, 96-97
M
returning, 82
ACOS, 97-98
mean values, 235
ASIN, 97
Macintosh platforms, 17
ATAN, 97
MEDIAN function, 83, 410
macros, Xcelsius support
ATAN2, 99
median, calculating, 83
for, 22
CEILING, 100
method of least squares, 255
Map components, 35-36
COS, 97-98
Microsoft Excel. See Excel
Augmented Map
DEGREES, 97, 99
Framework, 280-283
MID function, 118, 411
DOLLAR, 101
colorizing, 283-286
EVEN, 100
MIN function, 82, 410
Connected Maps reference
EXP, 103
minimum value,
implementation, 291-294
FALSE, 106
returning, 82
Data Insertion feature, 278
financial functions, 92
minus operator (-), 413
display data, 277-278
FLOOR, 100
MINUTE function, 111
Excel XML maps, 359-364
FORECAST, 104-105
multi-selection maps,
INT, 100
MIRR function, 94, 404
287-288
INTERCEPT, 105
MOD function, 101, 406
new features, 40
LN, 103
MODE function, 83, 410
obtaining further
LOG, 103
MONTH function, 111, 403
information with, 278-279
LOG10, 103
How can we make this index more useful? Email us at indexes@samspublishing.com
444
most common value in dataset, returning
most common value in
multiple screens controlled
NORMSINV function, 410
dataset, returning, 83
by label-based menu,
NOT function, 107, 405
mu (μ), 243
192-193
not-equal operator, 414
when to use, 199-200
multi-layer visibility
NOW function, 110, 403
activating visibility patterns
MXML-based
NPER function, 94, 404
with switch circuit, 197
components, 395
NPV function, 94, 404
designing by specification,
MXML code, connecting
193-197
ActionScript to, 385
numbers
group management,
absolute number, 96
198-199
converting text to numbered
N
multiple screens
equivalent, 105
controlled by label-based
random numbers,
N function, 105, 406
menu, 192-193
generating, 91-92
Name Manager window, 78
overview, 192
rounding, 100
named ranges, 77-79
when to use, 199-200
transposed digits,
negation operator (-), 81
detecting, 318-319
multi-selection maps, 36,
troubleshooting rounding
287-288
negative values,
displaying, 147
and truncation errors,
multiple data series, 301,
322-324
339-341
negatively directed
sliders, 162
multiple information sources
O
in single view, 37
net present value, 94-95
multiple screens, controlling
NETWORKDAYS function,
Object Browser pane
with label-based menus,
111-112, 403
definition of, 46
192-193
new features of Xcelsius
grouping components, 50
multiplication
2008, 39-41
overview, 48-50
operator (*), 414
existing component
positioning, 51
improvements, 40
multiplication tables, cell
occlusion, avoiding, 342-343
new components, list of, 40
references in, 327
Off option (chart
spreadsheet support, 39
mutually exclusive visibility,
scaling), 206
web connectivity, 41
190-192
OFFSET function, 2,
normal distribution, 92,
activating visibility patterns
119-122, 269-270, 309, 408
247-250
with switch circuit, 197
open-ended dashboards, 355
designing by specification,
NORMDIST function,
open-source dashboards,
193-197
92, 410
spreadsheets as, 13-16
group management,
NORMINV function,
198-199
92, 410
QUOTIENT function
445
operating systems,
parsing dates/time, 111
discrete probability
compatibility with
pasting components
distributions, 242-244
Xcelsius, 17
between XLF files, 63
normal distribution, 92,
operators, 80-81
247-250
percentage symbols in cell
conditional operators, 106
overview, 237
references, 76
logic operators, 107
probabilistic reasoning,
PI function, 97-98, 406
overview, 80
237-239
Pie Chart component, 54-56,
precedence, 81
uniform distribution, 92
139-140
table of, 412-414
PRODUCT function,
plus sign (+), 414
OR function, 107, 405
105, 406
PMT function, 94, 404
progress bars, 166
P
policy, cross-domain policy
smart progress bars,
files, 368-369
310-313
packaging
poorly positioned data,
projecting trends, 104-105
custom components,
troubleshooting, 324-325
Properties panel
387-391
population standard
Alerts tab, 24
dashboards, 354
deviation, 235-236
Appearance tab, 24
Panel Container
positioning panes/panels, 51
Behavior tab, 24
component, 58
General tab, 24
postfix operators, 81
panels
positioning, 51
POWER function, 102, 406
panel containers, 58
viewing, 22-24
Properties
PPMT function, 94, 404
property sheets,
definition of, 46
precedence of operators, 81
386-387, 394
positioning, 51
precision functions, 99-101
PV function, 94, 404
panes
prefix operators, 81
Components, 19
Print Button
Q
definition of, 46
component, 222
positioning, 51
Quick Start pane, 18, 46
printing, 40
views, 20, 47
QUOTIENT function,
Object Browser
probability
101, 406
definition of, 46
binomial distributions,
grouping
244-246
components, 50
conditional probability,
overview, 48-50
239-242
positioning, 51
continuous probability
Quick Start, 18, 46
distributions, 246
How can we make this index more useful? Email us at indexes@samspublishing.com
446
RADIANS function
R
percentage symbols in, 76
rounding numbers, 100
relative references, 74
ROUNDUP function,
RADIANS function,
region keys, 277
100, 407
97-98, 406
regions of maps, 276-277
RAND function, 91-92, 410
relative references, 74
S
RANDBETWEEN
remote connectivity of
function, 92
sample standard deviation,
desktop client portals, 37
random numbers,
235-236
REPLACE function, 118
generating, 91-92
sampling, 250-252
reports, aging, 264-266
range operator (:), 414
scaling
REPT function, 118, 411
ranges, named, 77-79
auto-scaling, 150
repurposing existing
charts, 204-206
RANK function, 89, 410
spreadsheets, 329-331
dials, 167-168
rank of numbers,
Reset Button
logarithmic scaling, 152-153
returning, 89
component, 223
scaling laboratory
RATE function, 94, 404
Reset Chart Scale option
dashboard, 150-152
ratio analysis, 266-267
(chart scaling), 206
sliders, 167-168
in dashboards, 39
troubleshooting, 328-329
resizing canvas, 48
ratio analyzer dashboard,
scaling laboratory dashboard,
267-270
restructuring text
150-152
Z score, 271-272
CONCATENATE
function, 119
SDK, installing, 376
ratio analyzer dashboard,
EXACT function, 119
SECOND function, 111, 403
266-270
FIND function, 118
Select a Range dialog
raw data, dealing with,
LEFT function, 117
box, 24-25
319-321
LEN function, 118
Selector components,
Rectangle component, 62
MID function, 118
59-60, 420
interactivity, 220-222
REPLACE function, 118
selector-style
new features, 40
REPT function, 118
spreadsheet functions
reference functions, 407-408
RGB color, specifying in
CHOOSE, 123
references (cell), 73
HTML, 220
HLOOKUP, 124-125
absolute references, 73-74
RIGHT function, 411
INDEX, 122-123
example, 75-76
ROUND function, 100, 406
MATCH, 123
hybrid cell references, 74-75
ROUNDDOWN
OFFSET, 119-122
isolating hardwired
function, 100, 407
VLOOKUP, 124-125
values, 76-77
rounding errors, 322-324
sensitivity analysis, 38
spreadsheets
447
shared component
sizing canvas, 48
Special Edition Using Excel
(Shared Component
slash (/), 412
2003, 71
Framework), 170
sliders
Special Edition Using Excel
Shared Component
BasicHorizontalSlider
2007, 71
Framework, 36, 169-171
connecting ActionScript
spinners, 175, 306-309
sharing dials, 169
and MXML code, 385
splash screens, 194
sigma (∑), 235
creating ActionScript
splicing text
code for, 378-384
SIGN function, 96, 407
CONCATENATE
generating SWF files
SIN function, 97-98, 407
function, 119
for, 385-386
EXACT function, 119
Single Value components
dual sliders, 164-166
FIND function, 118
dials
horizontal sliders, 160-161
LEFT function, 117
enhancing, 168-169
negatively directed
LEN function, 118
overview, 166-167
sliders, 162
MID function, 118
scaling, 167-168
overview, 159
REPLACE function, 118
Shared Component
scaling, 167-168
REPT function, 118
Framework, 169-171
smart sliders, 162-164,
sharing, 169
Spreadsheet Table
310-313
dual sliders, 164-166
component, 59, 224-225
vertical sliders, 160-161
gauges
spreadsheets
Sliding Picture Menu
example, 171-173
capabilities, 70-71
component, 211-212
overview, 171
cells
SLN function, 92, 404
wraparound gauges,
evaluating, 79-80
174-175
SMALL function, 89, 410
named ranges, 77-79
horizontal sliders, 160-161
smallest values in dataset,
references, 73-77
negatively directed
returning, 89
embedded formulas, 34
sliders, 162
smart sliders, 162-164,
formulas, 29-30
overview, 60, 158
310-313
chaining, 72
progress bars, 166
conditional formulas,
smart text, 302, 304-305.
smart sliders, 162-164
107-108
See also alerts
spinners, 175
simple formulas, 71-72
advantages of, 313
table of, 159
structure of, 71
spinners, 306-309
vertical sliders, 160-161
functions. See functions
text color, 305-306
Xcelsius product family
history of, 12-13
snapshots, Xcelsius product
comparison, 420
importing into Xcelsius, 30
family comparison, 418
SINH function, 407
logical values, 411-412
software. See specific software
How can we make this index more useful? Email us at indexes@samspublishing.com
448
Spreadsheets
as open-source dashboards,
virtual spreadsheet
statistical functions, 82-84,
13-16
compared to standalone
408-411
operators
Excel environment,
AVERAGE, 90
conditional
21-22
NORMDIST, 92
operators, 106
definition of, 13
NORMINV, 92
logic operators, 107
overview, 21
RAND, 91-92
overview, 80
viewing, 21
RANDBETWEEN, 92
precedence, 81
SQRT function, 80, 407
STDEV, 90-91
table of, 412-414
VAR, 90-91
Stacked Area Chart
repurposing existing
trend analysis
component, 135-136
spreadsheets, 329-331
FORECAST
stacked charts, 135-136
support for, 39
function, 252
text
standard deviation, 90-91,
forecasting trends,
formatting appearance
235-236
104-105
of, 116-117
STANDARDIZE
INTERCEPT
splicing and
function, 410
function, 252
restructuring, 117-119
statement of cash, 262
method of least
troubleshooting
squares, 255
statistical functions,
formula problems,
overview, 252
82-84, 408-411
325-327
Trend Analyzer
AVERAGE, 90
improperly structured
component, 252-255
NORMDIST, 92
data, 331-333
NORMINV, 92
STDEV function, 90-91, 410
poorly positioned data,
RAND, 91-92
subtraction operator (-), 414
324-325
RANDBETWEEN, 92
SUM function, 85, 410
raw data, 319-321
STDEV, 90-91
rounding and truncation
SUMIF function, 87-88, 410
VAR, 90-91
errors, 322-324
SUMPRODUCT function,
scaling issues, 328-329
statistics, 38. See
86-87, 411
transposed digits,
also probability
sums, calculating, 86-88
318-319
averages, 235
SUM function, 85
unclean data, 320-322
histograms, 231-234
SUMIF function, 87-88
underlying spreadsheet
importance of, 231
SUMPRODUCT
binding component
mean values, 235
function, 86-87
attributes to, 24-26
overview, 230-231
SUMSQ function, 88
definition of, 13, 46
sampling, 250-252
SUMSQ function, 88, 411
overview, 22
standard deviation, 235-236
SUMX2MY2 function, 411
Toggle Button component
449
SUMX2PY2 function, 411
smart text, 302, 304-305.
DAY function, 111
SUMXMY2 function, 411
See also alerts
DAYS360 function, 112
advantages of, 313
EDATE function, 112
SWF files, generating
spinners, 306-309
EOMONTH
for custom components,
text color, 305-306
function, 112
385-386
splicing and restructuring
HOUR function, 111
switch circuit, 197
CONCATENATE
MINUTE function, 111
switch map, 197
function, 119
MONTH function, 111
SYD function, 92, 404
EXACT function, 119
NETWORKDAYS
FIND function, 118
function, 111-112
LEFT function, 117
NOW function, 110
T
LEN function, 118
overview, 110
Tab Set component, 58,
MID function, 118
SECOND function, 111
182-184
REPLACE function, 118
TIME function, 110
REPT function, 118
TIMEVALUE
tables, 327
Xcelsius product family
function, 111
tally maps, 36, 288-289
comparison, 422
TODAY function, 110
TAN function, 97-98, 407
text-based components
WEEKDAY
TANH function, 407
labels, 53-54
function, 113
WEEKNUM
templates, 63-64
spinners, 306-309
function, 113
testing custom components,
TEXT function,
YEAR function, 111
391-393
116-117, 411
YEARFRAC
text components
text-related functions, 411
function, 113
converting to numbered
themes, 64-65, 131
TIME function, 110, 403
equivalent, 105
Ticker component, 211
time/date functions, 402-403
fonts
time
overview, 66-67
timelines
accounting for in financial
Xcelsius product family
adding data to, 132-134
analysis, 263-264
comparison, 417
timeline viewers, 37
aging reports, 264-266
formatting appearance of
TIMEVALUE function,
time and date awareness,
LOWER function, 116
111, 403
adding to dashboards
new features, 40
calendar arithmetic,
TODAY function, 110, 403
TEXT function, 116-117
113-115
Toggle Button
UPPER function, 116
DATE function, 110
component, 190
interactivity, 216-218
DATEVALUE
function, 110
How can we make this index more useful? Email us at indexes@samspublishing.com
450
toggling
toggling
Category III digitization
uniform look and feel,
mutually exclusive visibility,
errors, 318
designing
190-191
overview, 316-317
context switching, 62-63
visibility, 189-190
spreadsheets
fonts, 66-67
toolbars, 46
formula problems,
pasting components
325-327
between XLF files, 63
traffic light alerts, 210-211
improperly structured
templates, 63-64
transparency
data, 331-333
themes, 64-65
adjusting, 342
poorly positioned data,
union (,) operator, 80
in Rectangle
324-325
component, 221
updater facility
raw data, 319-321
(Shared Component
transposed digits, detecting,
repurposing existing
Framework), 171
318-319
spreadsheets, 329-331
UPPER function, 116, 411
Tree Map component,
rounding and truncation
148-149
errors, 322-324
Tree view, 20
scaling issues, 328-329
V
transposed digits,
trend analysis
318-319
Value at Risk (VaR), 258-261
FORECAST function, 252
unclean data, 320-322
VALUE function, 105, 407
forecasting trends, 104-105
INTERCEPT function, 252
TRUE function, 106
value-based alerts, 300
method of least squares, 255
TRUE value, 412
VaR (Value at Risk), 258-261
overview, 252
TRUNC function, 101, 407
VAR function, 90-91, 411
Trend Analyzer component,
truncation errors, 322-324
variance, estimating, 90-91
252-255
TYPE function, 109, 408
VBA code, Xcelsius
Trend Analyzer component,
support for, 22
252-255
U
VDB function, 92, 404
Trend Icon component,
Vertical Line
172, 224
uncertainty analysis, 39
component, 222
trialware packages, 18
unclean data, dealing with,
vertical lines, 62
trigonometry functions,
320-322
vertical sliders, 160-161
97-99
underlying spreadsheet
View menu commands, 19
troubleshooting
binding component
digitization of data
attributes to, 24-26
viewing
Category I digitization
definition of, 13, 46
line chart data, 343-344
errors, 317
overview, 22
Properties panel, 22, 24
Category II digitization
virtual spreadsheet, 21
uniform distribution, 92
errors, 317
XLF files, pasting components between
451
views
interface design, 154
X
Accordion view, 20-21, 47
line chart data, 343-344
Folder view, 47
multiple data series with
Xcelsius 2008
List view, 20, 47
similar values, 339-341
Engage, 37, 40
Tree view, 20
overview, 339
installation, 18
virtual spreadsheet
VLOOKUP function,
new features, 39-41
compared to standalone
124-125, 305, 326-327, 408
existing component
Excel environment, 21-22
improvements, 40
definition of, 13
new components,
W
overview, 21
list of, 40
viewing, 21
spreadsheet support, 39
Web connectivity, 41, 62, 422
web connectivity, 41
visibility
Web Services, 365-368
dynamic visibility, 50,
Xcelsius Data Manager, 355
WEEKDAY function,
188-189
Xcelsius product family
113, 403
hidden components, 50
comparison, 416
WEEKNUM function,
invisible components, 50
Art & Background
113, 403
managing, 38
components, 422
multi-layer visibility
Welcome screens, 194
art aids, 418-419
activating visibility
windows, Name Manager, 78
Chart components, 419
patterns with switch
Container components, 420
WORKDAY function, 403
circuit, 197
data update options, 417
workflows, custom
designing by
efficiency features, 417
component construction
specification, 193-197
export options, 418
workflow, 373
group management,
font support options, 417
198-199
workspace
general functionality, 416
multiple screens
customizing, 47
Map components, 421
controlled by label-
customizing layout
other components, 422
based menu, 192-193
canvas size, 48
Selector components, 420
overview, 192
Components Pane
Single-Value
when to use, 199-200
views, 47
components, 420
mutually exclusive visibility,
Object Browser, 48-50
snapshot options, 418
190-191
pane layout, 51
Text components, 422
overview, 188
definition of, 18
Web Connectivity
toggling, 189-190
elements of, 46
components, 422
visualizing data
World by Continent Map
Xcelsius SDK, installing, 376
avoiding occlusion, 342-343
component, 290
XLF files, pasting
filled radar charts with
wraparound gauges, 174-175
components between, 63
alerts, 341-342
How can we make this index more useful? Email us at indexes@samspublishing.com
452
XML
XML
Excel XML maps, 359-364
overview, 356
setting up for Xcelsius
with Excel 2003, 357-359
with Excel 2007, 357
XY charts, 141-145
Y-Z
YEAR function, 111, 403
YEARFRAC function,
113, 403
Z score, 271-272
Document Outline
- Introduction
- Getting What You Need from This Book
- Locating Specific Techniques Quickly
- How to Read This Book
- Accessing Legacy Xcelsius Files
- Best Practices Versus Shortcuts
- What This Book Covers
- Conventions Used in This Book
- 5 Using Charts and Graphs to Represent Data
- Choosing the Right Components for a Dashboard
- Building on Your Visualizations
- Viewing Grouped Data with Stacked Charts
- Working with XY and Bubble Charts
- Working with Tree Maps
- Issues and Techniques Related to Scaling
- Putting Visual Data Analysis into Focus
- Closing Thoughts
- Index
- A
- B
- C
- D
- E
- F
- G
- H
- I
- K-L
- M
- N
- O
- P
- Q
- R
- S
- T
- U
- V
- W
- X
- Y-Z
