Click here to see the SAS code.
Click here to see the example.
---------------------------------------------------------------
Note that this example is included in the book SAS/Graph: Beyond the Basics,
and all the 'tricks' used to create it are described in great detail!
---------------------------------------------------------------
This example was the *winner* of the 2005
DM Review magazine Data Visualization Contest,
Scenario 3 (dashboard)
Click here to see the contest scenario description.
There are 2 versions of the code - one that keeps the axes
for the wines & countries all to the same scale (see link above),
and one that scales each graph independently:
Click here to see this version of the dashboard.
Click here to see the SAS code.
And here's the code that did the individual drilldown plots (monthly level)...
Click here to see the SAS code.
Click here to see all the drilldown graphs, on one page.
---------------------------------------------------------------
Approach:
Since there will be many charts in a limited space in this
dashboard, I tried to use charts which are very simple,
straightforward, and consistent.
Since the 'target' values were provided by quarter, I decided to
summarize most of the data by quarter. I generate 17 bar charts
showing various quarterly metrics, and I overlay a special pointer
showing the target. I color-code these 17 charts consistently,
showing the good/satisfactory/poor performance.
Layout:
I placed the 'revenue' and 'profit' charts at the very top,
in a place of prominence, since they are the most important.
Each of the middle 3 rows group charts of similar metrics.
The sales pipeline and top 10 customers were a different sort of
data, so I used a different sort of chart for them, as well as a
different color scheme.
Here is a summary of the layout...
Row 1 = revenue & profit
Row 2 = measures/metrics (market share, order size, new customers, on time, and satisfaction).
Row 3 = type of wine (chardonnay, cabernet, merlot, sauvignan blanc, zinfandel).
Row 4 = region (North America, Asia, Europe, Middle East, and South America).
Row 5 = others (sales pipeline, and top 10 customers).
Axes:
I order the vertical bar charts by quarter
(Q1-Q4), and I order the horizontal bar charts so that the largest bar is
at the top (therefore the name/label of this bar is the first one in the
list of bars along the axis, when reading top-to-bottom).
The response axes on each
chart start at zero, and have a number of tickmarks/divisions
that best suits the data in that chart (rather than scaling them
all the same). Since "on time" values could go to 100% the
axis is scaled to 100%, since satisfaction values have a max
of 5 the axis has a max value of 5, and the other charts are
scaled up to the next 'nice' number that accommodates both
the maximum bar height and the target value (whichever is
higher).
Bars:
I colored the bars based on the evaluation criteria that was
provided in the spreadsheet. I chose light green for 'good',
pink for 'satisfactory', and bright red for 'poor' (the bright
red bars draw attention to these problem areas).
I use a medium gray outline for the bars, to separate their
light colors from the background, and I use the same medium
gray for the reference lines.
In the sales pipeline chart, I ordered the bar slices by highest probability
to lowest (with highest at the zero baseline), and shaded them so that the
highest probability sales were the darkest. This produces an optical
effect that reinforces the data -- the lightest color segments at the
end of the bars are the sales with least probability of happening.
In the Top 10 Customers bar chart, the blue segment of the bar represents
the year-to-date (YTD) sales, and the light segment represents sales
that are in the pipeline. I use a line in the blue segment to show
the quarter-to-date (QTD) sales as a part of the YTD sales. I created
a custom bar segment legend to show how the blue bar segment represents
both the YTD and QTD values. (YTD and QTD values were given in the
spreadsheet, and I subtracted the two to get the size of the YTD-QTD bar
segment correct).
Observations/Results:
Revenue & Profit are 'good' for all quarters (all green).
Market Share has been dropping and needs attention (lots of pink & red).
Sauvignan Blanc sales are 'odd' (high Q2 sales, and low Q3/Q4 sales).
Asian sales consistently exceed target (maybe need to raise target?)
The decline in 'Customer Satisfaction' might be related to the
decline in 'On Time Delivery'.
In the "Top 10 Customers" chart, it is odd that the QTD sales are
not approx 1/4 of the YTD sales (either a lot of sales are still to
come in the last few days of December, or these customers have
almost stopped buying wine during the 4th quarter.)
------------------------
Worth Mentioning:
Although this contest is only on the static gif image ...
The software I used generates an html overlay, which provides html
charttips/flyover-text, so when you hover your mouse over the bars,
you can see the exact/full values for that bar. I've also set up
html href drilldowns so you can click on the bars and that will bring
up the spreadsheet (I could have alternatively had it drilldown to a
table, report, or a more detailed graph). These capabilities are
very desirable in a dashboard.
Also, the way I set up this dashboard, I wrote a program to read the blocks
of data directly out of the spreadsheet, and transposed/summarized/joined
the data in various ways to get the data I plotted -- having done this
programmatically, the code can easily be re-used with other/similar data.
Back to Samples Index